0

I have an excel spreadsheet - First column contain ID numbers, 2nd column contains the name that the specific ID number should be assigned to.

The intention of my macro is to copy the ID number from A2, enter it into a web application, scrape the name associated with the ID number off of the web application and enter that name into the 3rd column.

I have successfully accomplished this with the very first value in A2.

My problem is that I can not for the life of me figure out how to use a loop so that the macro cycles through each cell in column A until it reaches a blank cell. I'm very new to VBA so forgive my ignorance. The code below is the piece of my macro that must loop. 

  IE.document.getElementById("caseId").Value = ThisWorkbook.Sheets("StarsID").Range("A2").Value

'Tells macro to click on 'Search Cases' Button
       IE.document.getElementById("_eventId_search").Click

'Tells macro to wait for browser to load completely
       Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop

'Tells macro that first search result will be entered into row 2
       y = 2
        currentQueue = IE.document.getElementById("results").Children(1).Children(0).Children(3).Children(0).innerText
        Sheets("StarsID").Cells(y, 3).Value = currentQueue
         ActiveCell.Offset(1, 0).Select
alowflyingpig
  • 730
  • 7
  • 18
JennyA
  • 1
  • 1
  • just to verify, you want to loop A2, A3..... until you get to an empty cell in Column A? Also, are there any blanks rows between non-empty ones? – AAA May 29 '19 at 21:14
  • AAA- Yes I would like to loop through A2,A3...until it reaches a blank row and I want it to stop. There are no empty rows between the non-empty ones. – JennyA May 30 '19 at 13:00

1 Answers1

0

try

y = 2
    Do While ThisWorkbook.Sheets("StarsID").Range("A" & y).Value <> ""
    IE.document.getElementById("caseId").Value = ThisWorkbook.Sheets("StarsID").Range("A" & y).Value
    IE.document.getElementById("_eventId_search").Click
    Do While IE.Busy = True Or IE.readyState <> 4: DoEvents: Loop
    currentQueue = IE.document.getElementById("results").Children(1).Children(0).Children(3).Children(0).innerText
    ThisWorkbook.Sheets("StarsID").Cells(y, 3).Value = currentQueue
    ActiveCell.Offset(1, 0).Select
    y = y + 1
    Loop

may add some wait time depending of the response of the website.

Ahmed AU
  • 2,757
  • 2
  • 6
  • 15
  • Hiya! so I tried the code above and unfortunately it's not picking up the value in cell A2 here: Do While ThisWorkbook.Sheets("StarsID").Range("A" & y).Value <> "" – JennyA May 30 '19 at 13:39