Solved I need to loop simple instructions till it hits an emty cell

March 27, 2013 at 13:26:33
Specs: Windows XP
Excel 2007 Loop
The number of rows would vary up to 200 rows. So I need an instruction to make it loop taking the accounts from column A, and pasting some dates in column B.
Please see the last 4 blocks of code... its the same instructions... just changing rows.
Thanks in advance!!!

Sub Macro7()
'
' Macro7 Macro
'
Dim attachmateapp As Object
Dim currsession As Object
Dim myscreen As Object

Set attachmateapp = CreateObject("Extra.System")
Set currsession = attachmateapp.Activesession
Set myscreen = currsession.Screen

        myscreen.SendKeys ("<Clear>")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Clear>")
        myscreen.waithostquiet timeout
        myscreen.SendKeys "amai"
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Clear>")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Clear>")
        myscreen.waithostquiet timeout
        myscreen.SendKeys "amai"
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        
        myscreen.SendKeys Range("A1")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date1 = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B1").Value = date1
        myscreen.waithostquiet timeout
        
        myscreen.SendKeys Range("A2")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date2 = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B2").Value = date2
        myscreen.waithostquiet timeout
        
        myscreen.SendKeys Range("A3")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date3 = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B3").Value = date3
        myscreen.waithostquiet timeout
        
        myscreen.SendKeys Range("A4")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date4 = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B4").Value = date4
        myscreen.waithostquiet timeout
        
   End Sub


See More: I need to loop simple instructions till it hits an emty cell

Report •


#1
March 27, 2013 at 17:35:43
The typical code to loop through a bunch of Rows is as follows:

Sub RowLoop()
'Determine Last Row With Data In Column A
   lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop Through Rows
    For rw = 1 To lastRw
'Execute Instructions, Row by Row
     Range("A" & rw).Copy _
      Destination:=Range("B" & rw)
    Next
End Sub

Seem like you would simply use the e.g. rw variable everywhere that you need to increment the value.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
March 28, 2013 at 05:32:55
I'm sorry... Im doing something wrong... I keep getting errors.
Do you mind writing it beggining to end?
I dont know where to place "date = myscreen.getstring(14, 48, 8)" becuase it would change depending on the account from column A.

Report •

#3
March 28, 2013 at 07:06:35
✔ Best Answer
Here is the section of code that I believe you want to loop. What I believe you want to do is update is the "1" for A1, date1 and B1, incrementing it for each Row.

   
        myscreen.SendKeys Range("A1")
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date1 = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B1").Value = date1
        myscreen.waithostquiet timeout

This is what I think should work, but I have no way of testing it. You'll notice that everywhere you have a hardcoded "1", I replaced it with the Concatenation Operator (&) and the variable that I chose represent the Row number within the loop.

e.g. when rw = 1, "A" & rw will be set to A1, date & rw will be set to date1, etc. The next time through the loop, when rw = 2, the code will use A2, date2, etc.

Sub RowLoop()
'Determine Last Row With Data In Column A
   lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop Through Rows
    For rw = 1 To lastRw
'Execute Instructions, Row by Row
        myscreen.SendKeys Range("A" & rw)
        myscreen.waithostquiet timeout
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        date & rw = myscreen.getstring(14, 48, 8)
        myscreen.waithostquiet timeout
        Range("B" & rw).Value = date & rw
        myscreen.waithostquiet timeout
    Next
End Sub

If you continue to get errors, you'll need to tell us what the error says and where it occurs. Keep in mind that we can't see your spreadsheet from where we're sitting, so you have to be very specific about what is happening.

The other issue is that you appear to be pulling data from some other application, therefore we can't replicate your environment for testing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
March 28, 2013 at 09:16:48
Ok, initually it was highliting "date & rw = myscreen.getstring(14, 48, 8)" in RED.
I changed it to "date = myscreen.getstring(14, 48, 8)" and now it works.
But something I cant understand is happening now...
I ran it with 150 accounts... gets me the info I need... but it also takes the last date it pulled and changes the time on my computer... isnt that weird???

Sub Macro7()
'
' Macro7 Macro
'
Dim attachmateapp As Object
Dim currsession As Object
Dim myscreen As Object
Dim screenname As String

Set attachmateapp = CreateObject("Extra.System")
Set currsession = attachmateapp.Activesession
Set myscreen = currsession.Screen

        myscreen.SendKeys ("<Clear>")
        myscreen.SendKeys ("<Clear>")
        myscreen.SendKeys "amai"
        myscreen.SendKeys ("<Enter>")
        myscreen.SendKeys ("<Clear>")
        myscreen.SendKeys ("<Clear>")
        myscreen.SendKeys "amai"
        myscreen.SendKeys ("<Enter>")
        'myscreen.waithostquiet timeout
        
'Determine Last Row With Data In Column A
   lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop Through Rows
    For rw = 1 To lastRw
'Execute Instructions, Row by Row
        myscreen.SendKeys Range("A" & rw)
        myscreen.SendKeys ("<Enter>")
        myscreen.waithostquiet timeout
        Date = myscreen.getstring(13, 48, 8)
        Range("B" & rw).Value = Date
        'myscreen.waithostquiet timeout
    Next
        
End Sub


Report •

#5
March 28, 2013 at 09:41:43
I changed "date" to ClosingDate".... I guess that was the small conflict I was having.
Works great now...

THANKS A LOT!!!!


Report •

#6
March 28, 2013 at 10:09:46
No, it's not wierd...

You will notice that the word "Date" is capitalized. That is because Date is a reserved word in VBA. As I said earlier, I couldn't test my suggestion, so I didn't catch that.

The Date statement in VBA sets the system date, so this line changes the system date every time it is executed:

Date = myscreen.getstring(13, 48, 8)

The Date function in VBA pulls the date from the system. Try this:

Sub Showdate()
   MsgBox "My System Thinks The Date Is: " & Date
End Sub

While your code appears to be working, in reality it isn't doing what you think it is.

What these 2 lines are actually doing are setting the system date, and then placing the system date in Range("B" & rw).

'Set System Date
   Date = myscreen.getstring(13, 48, 8)
'Put System Date in Column B
    Range("B" & rw).Value = Date

In the end, you are getting the result you wanted, but not in the manner that you think you are.

To fix the problem, simply change the variable that you are using. Instead of Date, use any non-reserved room, e.g.

'Set The Variable   
  myDate = myscreen.getstring(13, 48, 8)
'Put Value in Column B
   Range("B" & rw).Value = myDate

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question