I built a web form interface which I use to adjust orders on a web-based system our company uses.
The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.
Here is the code I use:
This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.
Did I do something wrong?
The code I have looks at a sheet, checks if the line needs adjusted, makes adjustments as needed, and then clicks a box for lines needing ordered.
Here is the code I use:
Code:
Sub Adjustorder()
Application.ScreenUpdating = False
Dim ie As Object
t = ThisWorkbook.Sheets("Adjust").Range("C3").Value 'checks for valid orders to adjust
If t = 0 Then MsgBox ("No Skus to add"): Exit Sub
Set ie = CreateObject("InternetExplorer.Application")
With ie
.Visible = True 'change to true to watch
.Navigate "localintranet"
Do Until .ReadyState = 4
DoEvents
Loop
.Navigate ThisWorkbook.Sheets("Adjust").Range("H4").Value 'This is the page for the orders to be modified
Do Until .ReadyState = 4
DoEvents
Loop
ThisWorkbook.Sheets("adjust").Range("A19:A30000").ClearContents
Dim D
For D = 1 To ThisWorkbook.Sheets("adjust").Range("C3").Value 'this performs the modificationa action based on the number of rows in the downloaded report.
With .document.forms(0)
Set Box = .document.all.Item(ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 1).Value)
Set Qty = .document.all.Item(ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 2).Value)
If ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 3).Value = True Then
Box.Click 'checks box of line needing ordered
Qty.Value = ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 4).Value 'changes order amount to correct amount per the metrics specified on the lot tab
End If
End With
ThisWorkbook.Sheets("Adjust").Range("A65536").End(xlUp).Offset(1, 0).Value = "Done" 'notes that modification has been compeleted
Next D
MsgBox ("all rows finished, please check and click 'Update Status' ")
End With
Set ie = Nothing
Application.ScreenUpdating = True
End Sub
This all works fine, but the amount of lines can be anywhere from 1 to 11,000. The fewer the amount of lines needed to loop through, the faster it does each line. With 11,000 lines it took nearly 1 second per line. With 200 lines it took 2 seconds for all of them.
Did I do something wrong?