VBA Loop

[alexdkinguk]

Hi all, can anyone help with the below? I can't get it to repeat the code but I don't know why.


'START LOOP HERE


Do Until Sheets("Sheet1").Range("A4").Value = ""
Set mytextfield = IE.document.all.Item("txtJobNo")
mytextfield.Value = Sheets("Sheet1").Range("A4").Value
'Next
'Press submit button
IE.document.getElementById("btnSubmit").Click

Application.Wait (Now + TimeValue("0:00:02"))
'Click on Stage 3
IE.document.getElementById("Image3").Click
Application.Wait (Now + TimeValue("0:00:02"))
'Click edit details
IE.document.getElementById("Submit1").Click

Application.Wait (Now + TimeValue("0:00:02"))

'Copy and paste the date from the spreadsheet.
Set mydatefield = IE.document.all.Item("txtDateWorksCompleted")
mydatefield.Value = Sheets("Sheet1").Range("B4").Text
'Click Submit
IE.document.getElementById("Button3").Click

Application.Wait (Now + TimeValue("0:00:02"))
'Click Continue
Set Continue = IE.document.all.Item("btnSubmit1")
Continue.Click
'IE.document.getElementByName("btnSubmit1").Click

Application.Wait (Now + TimeValue("0:00:02"))

'Click Close
Set Continue = IE.document.all.Item("btnSubmit1")
Continue.Click
'Delete row (THIS BIT WORKS)
Range("A4:B4").Select
Selection.Delete Shift:=xlUp
'lOOP BACK
Loop

End Sub

 

[Nine Zero]

not sure if im correct, but it could be because you are deleting the instance of the range A4 that you were initially checking to loop

so for example you said Do Until Range("A4").Value = ""

but in your last line before the loop, you are removing your range A4 entirely so it loses "sight" of that range ... not sure if that is the issue but thats the only thing I found at least...

 

[Joe4]

Welcome to the Board!

If what Nine Zero said is indeed the issue, maybe try this structure instead:

[COLOR=#333333]Do 
[/COLOR][COLOR=#ff0000]    If Sheets("Sheet1").Range("A4").Value = "" Then Exit Do[/COLOR][COLOR=#333333]
[/COLOR]
[COLOR=#333333]    Set mytextfield = IE.document.all.Item("txtJobNo")[/COLOR]
[COLOR=#333333]    mytextfield.Value = Sheets("Sheet1").Range("A4").Value[/COLOR]
[COLOR=#333333]    'Next[/COLOR]
[COLOR=#333333]    'Press submit button[/COLOR]
[COLOR=#333333]    IE.document.getElementById("btnSubmit").Click[/COLOR]

[COLOR=#333333]    Application.Wait (Now + TimeValue("0:00:02"))[/COLOR]
[COLOR=#333333]    'Click on Stage 3[/COLOR]
[COLOR=#333333]    IE.document.getElementById("Image3").Click[/COLOR]
[COLOR=#333333]    Application.Wait (Now + TimeValue("0:00:02"))[/COLOR]
[COLOR=#333333]    'Click edit details[/COLOR]
[COLOR=#333333]    IE.document.getElementById("Submit1").Click[/COLOR]

[COLOR=#333333]    Application.Wait (Now + TimeValue("0:00:02"))[/COLOR]

[COLOR=#333333]    'Copy and paste the date from the spreadsheet.[/COLOR]
[COLOR=#333333]    Set mydatefield = IE.document.all.Item("txtDateWorksCompleted")[/COLOR]
[COLOR=#333333]    mydatefield.Value = Sheets("Sheet1").Range("B4").Text[/COLOR]
[COLOR=#333333]    'Click Submit[/COLOR]
[COLOR=#333333]    IE.document.getElementById("Button3").Click[/COLOR]

[COLOR=#333333]    Application.Wait (Now + TimeValue("0:00:02"))[/COLOR]
[COLOR=#333333]    'Click Continue[/COLOR]
[COLOR=#333333]    Set Continue = IE.document.all.Item("btnSubmit1")[/COLOR]
[COLOR=#333333]    Continue.Click[/COLOR]
[COLOR=#333333]    'IE.document.getElementByName("btnSubmit1").Click[/COLOR]

[COLOR=#333333]    Application.Wait (Now + TimeValue("0:00:02"))[/COLOR]

[COLOR=#333333]    'Click Close[/COLOR]
[COLOR=#333333]    Set Continue = IE.document.all.Item("btnSubmit1")[/COLOR]
[COLOR=#333333]    Continue.Click[/COLOR]
[COLOR=#333333]
    'Delete row (THIS BIT WORKS)[/COLOR]
[COLOR=#333333]    Range("A4:B4").[/COLOR][COLOR=#333333]Delete Shift:=xlUp[/COLOR]
[COLOR=#333333]
    'lOOP BACK[/COLOR]
[COLOR=#333333]Loop[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[alexdkinguk]

Thanks both, I have run it with the suggested amendment but it still stops working at the point where it should be looping back. Have you any other ideas?

 

[Joe4]

You removed the part from the "Do" line of code, like I showed, right (you actually need to change the first few lines - just make sure you didn't miss that because I didn't make the first line red).

It is very difficult for us to test this without being in your environment and having access to your data.
Did you try stepping through the code line-by-line to see exactly where the issue is?
If you do this with the workbook open, so you can watch both at the same time, you may be able identify exactly what is going on and what the issue is.

 

[alexdkinguk]

Hi, yes I did remove that bit. I have just done the step through as suggested and figured out that the problem was that it needed a delay after it looped back. I have added the delay and it works perfectly now! Thank you for your help.

 

[Joe4]

You are welcome!

Glad to hear it is working now.
Yes, that is something we would not have been able to determine ourselves, because we don't know how quickly things are being updated on your side.
That "step through" debugging technique is really good tool to use, I use it a lot. It is often helpful to see what is happening on the sheet as it is running, and issues often become much more apparent.