Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with Case Structure

Status
Not open for further replies.
link99sbc

link99sbc

Technical User
Apr 8, 2009
141
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
r u saying that 09/ goes to 1,8???

please post your updated code for us to view...
 
Upvote 0 Downvote



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,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote


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


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote

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[b]
'these TWO have to move DOWN to where the ORIGINAL NEXT is
                next
              next[/b]

                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[red][b] <<=== HERE!!![/b][/red]


THINK man!!!

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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!!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



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

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



Have you tried to fix it?

If so, what did you try?

What was the result?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I tried too many things to list. nothing works.
I don't have any more time to mess with it.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

CADD6953
  • Locked
  • Question
Help needed with syntax/logic in Attachmate macro
2
Replies
26
Views
603
SkipVought
S
AlbertShenko
  • Locked
  • Question
Interacting with Attachmate dialog box at login 1
Replies
8
Views
318
SkipVought
S
d3sdinova
  • Locked
  • Question
Using VBA to work with two Attachmate sessions at the same time
Replies
9
Views
281
SkipVought
S
Sparky1157
  • Locked
  • Question
Running Calculator Demo with Macro
Replies
0
Views
106
Sparky1157
Sparky1157
Sparky1157
  • Locked
  • Question
Accessing Another Window (Windows Folder) with a Macro
Replies
3
Views
113
Sparky1157
Sparky1157

Part and Inventory Search

Sponsor

Back
Top