Loop function unexpectedly ended at line 20.

[Rpaikh]

Hello,


I have a trouble with my loop function where it show "Error occured while retrieving data" and the code is always ended at line 20. Can somebody help me if there's any thing wrong with my code so far ?

Dim r As Range, rall As Range
With ActiveSheet
    Set rall = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
For Each r In rall
    Set rdate = r


'Script name 1'


objSess.findById("wnd[0]").maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = [COLOR=#ff0000]rdate[/COLOR]
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/tbar[1]/btn[17]").press


Next r

 

[Rijnsent]

Hi Rpaikh,
I assume the values in the range are all values (no formulas)? Your code looks fine to me, I don't see anything weird (although you cut out quite a bit where you surf to the website etc.). And does your code end the first time it passes at the = rdate line (I assume that's line 20)? It could be that rdate.Value is needed to make it work.
Koen

 

[daverunt]

7436743 - doesn't show as a date in a date formatted cell if that's what it is supposed to be.
One too many digits or too far into the future.

 

[Rpaikh]

Hi Rpaikh,
I assume the values in the range are all values (no formulas)? Your code looks fine to me, I don't see anything weird (although you cut out quite a bit where you surf to the website etc.). And does your code end the first time it passes at the = rdate line (I assume that's line 20)? It could be that rdate.Value is needed to make it work.
Koen

Hello Rijnsent,

You're right there's something wrong with the rdate value in line 20. After I changed raw data in line 20 to be something else, it work as expected.

May I ask you if I want to add more raw data like rdate2 under column "B" and rdate3 under column "C" how the code would look like ?

Dim r As Range, rall As Range
With ActiveSheet
    Set rall = .Range("a2", .Range("a" & .Rows.Count).End(xlUp))
End With
For Each r In rall
    Set rdate = r


'Script name 1'


objSess.findById("wnd[0]").maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = [COLOR=#ff0000]rdate
[/COLOR]objSess.findById("wnd[0]/tbar[1]/okcd").Text = [COLOR=#ff0000][FONT=Verdana]rdate2
[/FONT][/COLOR]objSess.findById("wnd[0]/tbar[2]/okcd").Text = [COLOR=#ff0000][FONT=Verdana]rdate3[/FONT][/COLOR]
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/tbar[1]/btn[17]").press


Next r

 

[Rpaikh]

7436743 - doesn't show as a date in a date formatted cell if that's what it is supposed to be.
One too many digits or too far into the future.

Hello daverunt,


Thank for your help. This fix my issue!

 

[Rijnsent]

Hi Rpaikh,
if you want those, you could do it like this:
Koen

Dim r As Range, rall As Range

    Set rall = ActiveSheet.Range("a2", .Range("a" & .Rows.Count).End(xlUp))
'This is a range of one column
For Each r In rall
    rdate = r.value
    rdate2 = r.offset(0,1).value 'column next to it
    rdate3 = r.offset(0,2).value 'column C

'Script name 1'

objSess.findById("wnd[0]").maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = rdate
objSess.findById("wnd[0]/tbar[1]/okcd").Text = rdate2
objSess.findById("wnd[0]/tbar[2]/okcd").Text = rdate3
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/tbar[1]/btn[17]").press


Next r

 

[Rpaikh]

This perfectly work! Thank you once again !

Hi Rpaikh,
if you want those, you could do it like this:
Koen

Dim r As Range, rall As Range

    Set rall = ActiveSheet.Range("a2", .Range("a" & .Rows.Count).End(xlUp))
'This is a range of one column
For Each r In rall
    rdate = r.value
    rdate2 = r.offset(0,1).value 'column next to it
    rdate3 = r.offset(0,2).value 'column C

'Script name 1'

objSess.findById("wnd[0]").maximize
objSess.findById("wnd[0]/tbar[0]/okcd").Text = rdate
objSess.findById("wnd[0]/tbar[1]/okcd").Text = rdate2
objSess.findById("wnd[0]/tbar[2]/okcd").Text = rdate3
objSess.findById("wnd[0]").sendVKey 0
objSess.findById("wnd[0]/tbar[1]/btn[17]").press


Next r