Looping through connector

Support
#1

I have a long list of keywords on Sheet1 column 1. I'd like to insert that keyword into the searchmetrics data connector as something like this =Dump(Connector("Searchmetrics.ResearchOrganicGetListRankingsKeyword",A1,"US","url,position,page",TRUE,50)) outputting on a new sheet, each keyword with the associated URL Position Page.

I've tried to loop through but i'm a bit shaky on arrays in VBA. I've also tried to paste the formula every 50 some rows, but it's sloppy and excel seems quite unstable when I do this.

Can anyone offer some basic advice or code snippet on how I can step through a long list of keywords and output them in a nice sheet with the connector dumped date? My next step is to do some fun pivot tables, but having a lot of trouble getting it all in the correct format.

Thank you in advance
-Jim T

#2

My VBA is amateur-level at best, but I put together this code for your situation. Keywords go in column A (no header) and adjust the RowNum values accordingly. The array formula should go in every 50th row and fetch the string from column A.

Code:

Sub Repeat()
Dim ws As Worksheet
Dim lCount As Long, lNum As Long
   For RowNum = 2 To 200 Step 50
   For lCount = 1 To 1 Step 1
   lNum = lNum + 1
    Range(Cells(RowNum, 2), Cells(RowNum, 2)).Formula = "=Dump(Connector(""Searchmetrics.ResearchOrganicGetListRankingsKeyword"",A" & lNum & ",""US"",""url,position,page"",TRUE,50))"
    Next lCount
   Next RowNum
End Sub
1 Like