Need a loop solution

TNTScrub

New Member
Joined
Jan 7, 2021
Messages
14
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello Heros, I need to perform a kinda loop. Specifically I need to select a cell, then perform some code, and then select the next cell and do it all over again. Now I know cell selection is a bit of a no no when ever it can be avoided but I'm working with an antiquated intranet page on IE and so unfortunately I need the selection. Basically I have a set of numbers in Col A with a bunch of info in the subsequent columns to feed into the web page (I can do this with out a problem). What I need to happen is when that task is complete to select the next cell and then perform the same task. Can anyone help me?

Sub I_Cant_Make_Loops()

ActiveCell.do some code type stuff
ActiveCell.Offset(1, 0).Select

'then repeat the process

End Sub

Sorry I don't have better code to help but everything I have tried is clearly worthless.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Can you please define your problem in more detail?
What will be the starting cell? A1? A2?
And how far down will we going? Or do we just stop at the last populated cell in column A?
 

TNTScrub

New Member
Joined
Jan 7, 2021
Messages
14
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Can you please define your problem in more detail?
What will be the starting cell? A1? A2?
And how far down will we going? Or do we just stop at the last populated cell in column A?
I apologize for the lack of detail. Currently Ive managed to make this kinda work using an event handler and several call outs. Ill be starting in A2 and cycling down to the last populated row.
I've pasted my various codes below and theres a lot to take in but currently it kinda works. The first sub opens IE and logs me into the Database, turns on the event handler and calls out to the next sub. The second sub simply checks to see if the cell is populated with text and if so calls out to the third sub. Sub three which is my biggest pain takes the data from my spreadsheet and enters it into the web page, saves it and moves to the next cell where the event handler takes over. The event handler calls out to Sub two causing it to run the entire process again while skipping the login step from sub one. The problem I'm trying to solve is that Sub three which enters the data into the web data base opens a new instance of IE each time it performs a task and then closes it each time its done. I would prefer to not do this as it introduces way to many opportunities for errors and hang ups. I would prefer to have sub one contain a loop that would perform the task while redirecting urls instead of having to use event handlers and some convoluted way of performing this task. My second larger issue is that sub three uses sendkeys and its only mildly reliable at best. Either way I'm basically attempting to loop all the code in sub three and that's my biggest hang up at the moment. I've also added a screen shot of the spread sheet that I'm using to do all of this.

Sub LOGIN()
Dim ie As InternetExplorer
Set ie = New InternetExplorer
Application.EnableEvents = True
ie.Visible = True
ie.Navigate2 " URL Goes Here to log me into the web based database"
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))
ie.FullScreen = True
ie.Document.all("TextBox_UserName").Value = "My User Name "
ie.Document.all("TextBox_LoginPassword").Value = "My Pass Word"
ie.Document.all("Button_Login").Click
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:02"))

Call CellChecks


End Sub



Sub CellChecks()

Dim rCell As Range
Set rCell = ActiveCell
For Each Cell In rCell
If Len(rCell.Formula) = 0 Then


MsgBox "List Complete"


Else

Call Cycle_Assignment

End If
Next
End Sub



Sub Cycle_Assignment()

Dim ie As InternetExplorer
Dim i As Variant
Dim Enumber As String
Enumber = ActiveCell.Offset(0, 3).Value
Set ie = New InternetExplorer
On Error Resume Next
ie.Visible = True
ie.FullScreen = True
ie.Navigate2 "This opens a New IE and directs to a different page after I log In"
Do While ie.Busy: DoEvents: Loop
Do Until ie.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop
ie.Document.all("TextBox_TagNum").Value = ActiveCell.Value
ie.Document.all("ddlFacility").Value = "TH"
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "{TAB 7}", True
Application.Wait (Now + TimeValue("0:00:05"))
Application.SendKeys "~"
Application.Wait (Now + TimeValue("0:00:05"))
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_0").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_1").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_2").Value = Enumber
ie.Document.all("GridView_CyclesFound_DropDownList_NewTech_3").Value = Enumber
Application.Wait (Now + TimeValue("0:00:05"))
ie.Document.all("Button_UpdateAll").Click
Application.Wait (Now + TimeValue("0:00:05"))
ie.Quit
ActiveCell.Offset(1, 0).Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastPopulatedRow As Long
LastPopulatedRow = Range("A" & Rows.Count).End(xlUp).Row
If Intersect(Target, Range("A2: " & "A" & LastPopulatedRow)) Is Nothing Then
Application.EnableEvents = False
MsgBox "List Complete"

Else
CellChecks

End If
End Sub

1611325247482.png
 

Watch MrExcel Video

Forum statistics

Threads
1,129,513
Messages
5,636,780
Members
416,939
Latest member
Rajakumaran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top