Create Query table based on cell value

Question

I want the macro to run from top to bottom on column C (on Sheet "Tracker") to find any cells that match the "Waiting", "Deferred", "New", or "Pending" text.

Each time it finds a match I want to run a web query on Sheet "X" with the address:

"http://efm/telecom/engineeringWorkOrders/viewEWOStatusLog.aspx?ewonumber=" + the corresponding value in column A from the matched cell in column C.

If (in Sheet "Tracker") Range("C" & CurRow).Value is "New" Then
    Run a web query in Sheet "X" (Starting in cell A1) with the address: "http://efm/telecom/engineeringWorkOrders/viewEWOStatusLog.aspx?ewonumber=" & Range("A" & CurRow).Value

score 0 · Accepted Answer · answered Jul 15 '15 at 18:46

I don't know much about your query conditions as you don't list them, but this will handle your loop and output needs:

Sub QueryIt()
    Dim CurRow As Long, LastRow As Long, DestRow As Long

    LastRow = Sheets("Tracker").Range("C" & Rows.Count).End(xlUp).Row
    For CurRow = 1 to LastRow
        If Sheets("Tracker").Range("C" & CurRow).Value = "New" Then
            DestRow = Sheets("X").Range("A" & Rows.Count).End(xlUp).Row + 1
            'Query command with address: "http://efm/telecom/engineeringWorkOrders/viewEWOStatusLog.aspx?ewonumber=" & Sheets("Tracker").Range("A" & CurRow).Value
            'Output query to Sheets("X").Range("A" & DestRow).Value
        End If
    Next CurRow
End Sub

I finished compiling the code but I get this error:"Compile error: End If without block If" ... I may have typed something out of place :( — VladM, Jul 15 '15 at 20:01
@VladM suggest an edit to my answer and add your code below mine — Chrismas007, Jul 15 '15 at 20:25

Create Query table based on cell value

1 Answers1