How do I send a list of cell values to a website in an excel macro?

February 21, 2024

I have a column of cells that I need to send to a website, one at a time, hitting the enter key between data entries. My data starts in cell "E4" and has a variable number of possible entries, up to 1000. I would like to send "E4" to the website and hit enter, then send "E5" and hit enter, repeating down the "E" column until I reach an empty cell.

I have managed to produce the desired result by utilizing SendKeys, but don’t know how to make SendKeys function with looping. Loop will end with a blank cell.

SendKeys ActiveSheet.Range("E4").Value
Application.Wait Now + TimeValue("00:00:001")
SendKeys "~"
Application.Wait Now + TimeValue("00:00:001")

SendKeys ActiveSheet.Range("E5").Value
Application.Wait Now + TimeValue("00:00:001")
SendKeys "~"
Application.Wait Now + TimeValue("00:00:001")

SendKeys ActiveSheet.Range("E6").Value
Application.Wait Now + TimeValue("00:00:001")
SendKeys "~"
Application.Wait Now + TimeValue("00:00:001")

>Solution :

Something like this maybe:

Sub SendAll()
    Dim c As Range
    
    Set c = ActiveSheet.Range("E4") 'starting cell
    Do While Len(c.Value) > 0
        SendKeys c.Value
        Application.Wait Now + TimeValue("00:00:001")
        SendKeys "~"
        Application.Wait Now + TimeValue("00:00:001")
        
        Set c = c.Offset(1) 'next cell down
    Loop
    
End Sub