Dynamic web query question

[gareths]

I am currently using a web query to find the postcodes from an external site:

Private Sub commandbutton1_click()
    www = "URL;http://api1.nearby.org.uk/api/convert.php?key=431b753ede6408&p=" _
    & Range("U2") & "&want=postcode-uk&output=text"
    With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=Range("W2"))
 
        .Name = "convert.php?key=431b753ede6408&p=""""&want=postcode-uk&output=text"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """postcodes"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

I'm just wondering if I'm able to change W2 so it is the active cell and U2 so it is the active cell - 2.

 

[John_w]

What do you mean by 'active cell - 2'? 2 rows before, 2 columns before, its value -2, etc.?

Something like this may help. Use negative Offset arguments to get 2 rows/columns before. I've assumed you mean 2 rows before.

    www = "URL;http://api1.nearby.org.uk/api/convert.php?key=431b753ede6408&p=" _
    & ActiveCell.Offset(-2, 0) & "&want=postcode-uk&output=text"
    With ActiveSheet.QueryTables.Add(Connection:=www, Destination:=ActiveCell)

 

[gareths]

I meant column - I amended the code accordingly and it is now working, thanks a lot!