I am having trouble getting the cell.value to go to a spot in excel. I've been trying to convert a vba excel macro to vba selenium since the site is no longer compatible with internet explorer.

This is what I've done so far:

Public Sub members()

    Dim rng As Range
    Dim rng1 As Range
    Set cd = New Selenium.ChromeDriver
    Dim cell As Range
    Dim By As Selenium.By
    Set By = New Selenium.By
    Dim keys As Selenium.keys
    
    Dim p As String
    Dim b As String
    
    Set rng = Range(Worksheets("sheet1").Range("A2"), Worksheets("Sheet1").Range("A2").End(xlDown))
    
    cd.Start
    cd.Get "https://partneroptumfitness.com"
    
    For Each cell In rng
        cd.Get "/"
        cd.FindElementById("code").SendKeys (cell.Value)
        cd.FindElementByXPath("//form[@id='submit-code']/button").Click
        
        cd.Wait 1000
        
        If cd.IsElementPresent(By.XPath("//div[@id='__next']/div[2]/div/div/div[2]/div/p")) Then
            p = cd.FindElementByXPath("//div[@id='__next']/div[2]/div/div/div[2]/div/p").Text
            cell.Value = p
            
            cd.Wait 1000
            Else
                b = "Code not found"
                cell.Value = b
                cd.Refresh
            End If
        
    Next
    

End Sub

It does work, it fills in the data from the cell in range A2, clicks submit & then grabs the element that results but instead of writing it to let's say B2, it just overwrites A2. I've tried changing cell.value to a different range & assigning it to Worksheets("Sheet1").Range(B2) but it doesn't work.

I'm sure it is an easy solution but I am completely stumped...

Thanks for your help!

CodePudding user response：

For Each cell In rng

reads each cell in the range A2:[end of data].

If you then do this:

cell.Value = b

then that's going to overwrite whatever you just read from cell

Maybe you meant to use:

cell.Offset(0, 1).Value = b

which would place b in Column B on the same row as cell.