×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help with Case Structure

Need help with Case Structure

Need help with Case Structure

(OP)
I can't get the Case structure right.  Tried several different ways.  It works with 07 and 09 but not 08.

    lRowCount = InputBox("How many rows to process?")
    ActiveCell.Offset(0, 0).Activate

        If lRowCount > 0 Then
        Do While lOff <= lRowCount
             
            oScrn.PutString Left(ActiveCell.Offset(lOff, 0).Value, 3), 3, 5 'acctnbr
            oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value, 4), 3, 9 'acctnbr
            
            For iCol = 1 To 12
              Select Case iCol
              
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7
                Case 0
                   oScrn.PutString "08/" & iCol - 4, 4, 7
                Case Else
                   oScrn.PutString "09/" & iCol - 4, 4, 7
                
              End Select
                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<PF8>")
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                oScrn.SendKeys ("<Enter>")
                
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                               
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(20, 74, 22, 78).Value
                                   
            Next
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3, 19, 3, 63).Value
            lOff = lOff + 1
        Loop
 

RE: Need help with Case Structure

Case Is <= 4
can be any number less than or equal to 4...0 (zero is less than 4)


try it this way instead

CODE

                Case 0
                   oScrn.PutString "08/" & iCol - 4, 4, 7
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7

                Case Else
                   oScrn.PutString "09/" & iCol - 4, 4, 7

RE: Need help with Case Structure

In addition to the fact that Case is <=4 will never allow Case 0 to execute, iCol NEVER HAS A VALUE OF ZERO!

CODE

            For iCol = 1 To 12
              Select Case iCol
              
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7
                Case 0
                   oScrn.PutString "08/" & iCol - 4, 4, 7
                Case Else
                   oScrn.PutString "09/" & iCol - 4, 4, 7
Maybe what you want???

CODE

            For iCol = 1 To 12
              Select Case iCol
              
                Case 1
                   oScrn.PutString "08/" & iCol - 4, 4, 7
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7
                Case Else
                   oScrn.PutString "09/" & iCol - 4, 4, 7
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
Tried this but no working
'07/1-07/4 & 08/1-08/4 & 09/1-09/4

appears to be something off with the counter

09/ goes 1-8

RE: Need help with Case Structure



Quote:

tried this...
Have absolutely no idea what you mean???

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
Fixed it. Works fine now. Unless you have a better way.
Thanks for the help.


For iCol = 1 To 12
              Select Case iCol
              '07/1-07/4 & 08/1-08/4 & 09/1-09/4
                                   
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7
                Case Is <= 8
                   oScrn.PutString "08/" & iCol - 4, 4, 7
                Case Else
                   oScrn.PutString "09/" & iCol - 8, 4, 7

RE: Need help with Case Structure

r u saying that 09/ goes to 1,8???
 
please post your updated code for us to view...

RE: Need help with Case Structure




so iCol ...

1 to 4
                Case Is <= 4
                   oScrn.PutString "07/" & iCol, 4, 7
5 to 8
                Case Is <= 8
                   oScrn.PutString "08/" & iCol - 4, 4, 7
9 to 12
               Case Else
                   oScrn.PutString "09/" & iCol - 8, 4, 7  

YES?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
yes
 col1 | col2  | col3  | col4  | col5  | col6 | col7  | col8  | col9  | col10 | col11 | col12
071/1| 07/2 | 07/3 | 07/4 | 08/1 | 08/2 | 08/3 | 08/4 | 09/1 | 09/2  | 09/3  | 09/4

RE: Need help with Case Structure



CODE

for j = 7 to 9
  for iCol = 1 to 4
     oScrn.PutString format(j,"00") & "/" & iCol, 4, 7
  next
next
 

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
Very Cool
I assume this takes the place of Whole CASE Structure?

Sub Checks()
    Dim Sessions As Object
    Dim System As Object
    Dim Sess0 As Object
    Dim oScrn As Object
    
    Dim lRowCount, lOff As Long, iCol As Integer

    Set System = CreateObject("EXTRA.System")
    Set Sess0 = System.ActiveSession
    Set oScrn = Sess0.Screen
    
    lRowCount = InputBox("How many rows to process?")
    ActiveCell.Offset(0, 0).Activate

        If lRowCount > 0 Then
        Do While lOff <= lRowCount
             
            oScrn.PutString Left(ActiveCell.Offset(lOff, 0).Value, 3), 3, 5 'acctnbr
            oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value, 4), 3, 9 'acctnbr
            
            'For iCol = 1 To 12
             ' Select Case iCol
                                   '07/1-07/4 & 08/1-08/4 & 09/1-09/4
              '  Case Is <= 4
              '    oScrn.PutString "07/" & iCol, 4, 7
              '  Case Is <= 8
              '     oScrn.PutString "08/" & iCol - 4, 4, 7
              '  Case Else
              '     oScrn.PutString "09/" & iCol - 8, 4, 7
              'End Select

              for j = 7 to 9
               for iCol = 1 to 4
                oScrn.PutString format(j,"00") & "/" & iCol, 4, 7
                next
              next

                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<PF8><PF8>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                oScrn.SendKeys ("<Enter>")
                
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(20, 72, 24, 79).Value
            Next
            
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3, 72, 3, 79).Value
           
            ActiveCell.Offset(lOff, iCol + 1) = _
                    oScrn.Area(3, 19, 3, 63).Value
            
            lOff = lOff + 1
        Loop
    End If


    Set oScrn = Nothing
    Set Sess0 = Nothing
    Set System = Nothing
End Sub
 

RE: Need help with Case Structure




That is does.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
skip, you might want to look at this again.
it's not working right. not getting enough columns
can't tell for sure but looks like 09 is over writing 08
got an error on one of the next lines.

I'm running it now using the case statements
i'll have to try it again later today or tomorrow.

RE: Need help with Case Structure


you have your NEXT in the wrong place...

CODE

            'For iCol = 1 To 12
             ' Select Case iCol
                                   '07/1-07/4 & 08/1-08/4 & 09/1-09/4
              '  Case Is <= 4
              '    oScrn.PutString "07/" & iCol, 4, 7
              '  Case Is <= 8
              '     oScrn.PutString "08/" & iCol - 4, 4, 7
              '  Case Else
              '     oScrn.PutString "09/" & iCol - 8, 4, 7
              'End Select

              for j = 7 to 9
               for iCol = 1 to 4
                oScrn.PutString format(j,"00") & "/" & iCol, 4, 7
'these TWO have to move DOWN to where the ORIGINAL NEXT is
                next
              next


                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<PF8><PF8>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                oScrn.SendKeys ("<Enter>")
                
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(20, 72, 24, 79).Value
            Next <<=== HERE!!!


THINK man!!!

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
Yea I did that before but got an error on the Loop below.

         lOff = lOff + 1
        Loop
    End If

Must be an excel glitch.  I closed it out then opened it and tried it again. works fine now.

I need a drink.

Thanks Skip!!

RE: Need help with Case Structure

(OP)
no it doesn't
this compiles but 08 and 09 over writes 07
so I'm only getting the first 4 columns

Sub Checks()
    Dim Sessions As Object
    Dim System As Object
    Dim Sess0 As Object
    Dim oScrn As Object
    
    Dim lRowCount, lOff As Long, iCol As Integer

    Set System = CreateObject("EXTRA.System")
    Set Sess0 = System.ActiveSession
    Set oScrn = Sess0.Screen
    
    lRowCount = InputBox("How many rows to process?")
    ActiveCell.Offset(0, 0).Activate

        If lRowCount > 0 Then
        Do While lOff <= lRowCount
             
            oScrn.PutString Left(ActiveCell.Offset(lOff, 0).Value, 3), 3, 5 'acctnbr
            oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value, 4), 3, 9 'acctnbr
            
            'For iCol = 1 To 12
            '  Select Case iCol
                                   '07/1-07/4 & 08/1-08/4 & 09/1-09/4
            '    Case Is <= 4
            '      oScrn.PutString "07/" & iCol, 4, 7
            '    Case Is <= 8
            '       oScrn.PutString "08/" & iCol - 4, 4, 7
            '    Case Else
            '       oScrn.PutString "09/" & iCol - 8, 4, 7
            '  End Select
              
              For j = 7 To 9
               For iCol = 1 To 4
                oScrn.PutString Format(j, "00") & "/" & iCol, 4, 7
                
                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<PF8><PF8>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                oScrn.SendKeys ("<Enter>")
                
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(20, 72, 24, 79).Value
               Next
             Next
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3, 72, 3, 79).Value
           
            ActiveCell.Offset(lOff, iCol + 1) = _
                    oScrn.Area(3, 19, 3, 63).Value
            
            lOff = lOff + 1
        Loop
    End If


    Set oScrn = Nothing
    Set Sess0 = Nothing
    Set System = Nothing
End Sub
 

RE: Need help with Case Structure




Step thru & DEBUG you code so you can "SEE" what's happening.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
I tried this code again and I only get 4 columns instead of 12.  071-074 in the first for columns.  Then 081-084 overwrites the columns.  Then 091-094 overwrites the same columns again.  The code compiles and I get no errors.
071-074 should be in the first four columns then 081-084 in the next 4 columns then 091-094 in the next 4 columns.  A total of 12 columns.

Sub Checks()
    Dim Sessions As Object
    Dim System As Object
    Dim Sess0 As Object
    Dim oScrn As Object
    
    Dim lRowCount, lOff As Long, iCol As Integer

    Set System = CreateObject("EXTRA.System")
    Set Sess0 = System.ActiveSession
    Set oScrn = Sess0.Screen
    
    lRowCount = InputBox("How many rows to process?")
    ActiveCell.Offset(0, 0).Activate

        If lRowCount > 0 Then
        Do While lOff <= lRowCount
             
            oScrn.PutString Left(ActiveCell.Offset(lOff, 0).Value, 3), 3, 5 'acctnbr
            oScrn.PutString Right(ActiveCell.Offset(lOff, 0).Value, 4), 3, 9 'acctnbr
            
            'For iCol = 1 To 12
            '  Select Case iCol
                                   '07/1-07/4 & 08/1-08/4 & 09/1-09/4
            '    Case Is <= 4
            '      oScrn.PutString "07/" & iCol, 4, 7
            '    Case Is <= 8
            '       oScrn.PutString "08/" & iCol - 4, 4, 7
            '    Case Else
            '       oScrn.PutString "09/" & iCol - 8, 4, 7
            '  End Select
              
              For j = 7 To 9
               For iCol = 1 To 4
                oScrn.PutString Format(j, "00") & "/" & iCol, 4, 7
                
                oScrn.MoveRelative 1, 1, 1
                oScrn.SendKeys ("<PF8><PF8>")
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                oScrn.SendKeys ("<Enter>")
                
                Do While Sess0.Screen.OIA.Xstatus <> 0
                    DoEvents
                Loop
                ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(20, 72, 24, 79).Value
               Next
             Next
            ActiveCell.Offset(lOff, iCol) = _
                    oScrn.Area(3, 72, 3, 79).Value
           
            ActiveCell.Offset(lOff, iCol + 1) = _
                    oScrn.Area(3, 19, 3, 63).Value
            
            lOff = lOff + 1
        Loop
    End If


    Set oScrn = Nothing
    Set Sess0 = Nothing
    Set System = Nothing
End Sub
 

RE: Need help with Case Structure




Have you tried to fix it?

If so, what did you try?

What was the result?

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Need help with Case Structure

(OP)
I tried too many things to list. nothing works.
I don't have any more time to mess with it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close