Excel VBA trying to loop through rows in a named range

Dale Logan
Dale Logan used Ask the Experts™
on
Experts,

When you see my file, you will know right away that I am clueless when it comes to using VBA in Excel.

I created a macro that goes out to a website and looks up a small amount of data for one golfer. Now I am trying to figure out how to do this for all golfers in a table. If I could figure out how to loop through all users I would then have to figure out to have the data pasted below the previous golfers data. The data for each golfer should span 3 rows. So, I need to be able to go down 3 or 4 rows before pasting the next golfer.

Thanks, Dale

Handicaps.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I haven't looked at your code yet but to loop through a named range

Dim rngRow As Range
For Each rngRow In Range("MyNamedRange").Rows
    Debug.Print rngRow
Next

Open in new window

Dale LoganConsultant

Author

Commented:
Ok, I think I've added that part correctly, but can't be sure until I get everything else fixed.

Each time the loop runs I need a url inserted. The only thing different each time is the value in the third column of the named range needs to be inserted in the middle.

Here's how I am trying to do that:

Dim String1 As String
Dim String2 As String

String1 = "URL;http://widgets.ghin.com/HandicapLookupResults.aspx?entry=1&dynamic=&small=0&css=default&ghinno="
String2 = "hidename=0&showmsg=0&showheader=1&showtabheader=0&combinehieff=0&showheadertext=1&showfootertext=1&tab=2"


String1&rngRow.Columns(3).Value&String2

Open in new window

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
That should work. (I know the rngRow.Columns(3) part is correct)
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Dale LoganConsultant

Author

Commented:
I must not be putting the url string together correctly. I get the attached error message in the attached section.
Capture3Capture4
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
There shouldn't be quotes around String1 & rngRow.Columns(3).Value & String2.
Dale LoganConsultant

Author

Commented:
Wow. It looks like we are really close. The only thing left to figure out is to have the loop drop down 3 cells each time through. Currently it is overwriting the previous loops results each time. I have a line in there that says: Destination:=Range("$A$1")). How would I make it drop down 3 cells each time?
Dale LoganConsultant

Author

Commented:
Got it figured out. Thanks so much for your help.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial