Hi all,

I'm trying to adapt a macro. I have a basic index/ match lookup from one worksheet to another. It returns the value by each column. Example below:

Dim ColumnA As Range: Set ColumnA = Application.Index(WS1.Range("A:A"), Application.Match(WS2.Cells(r, 5), WS1.Range("Q:Q"), 0))
Dim ColumnB As Range: Set ColumnB = Application.Index(WS1.Range("B:B"), Application.Match(WS2.Cells(r, 5), WS1.Range("Q:Q"), 0))
What I'm trying to do is put this within a loop. As the loop counts through the columns, the column index will change. I was trying different variations like this but I'm missing something:

i = 0
    For ColumnCount = 1 To WS1.Columns.Count
        Dim AnyColumn As Range: AnyColumn = Application.Index(WS1.Column(i), Application.Match(WS2.Cells(r, 5), WS1.Range("Q:Q"), 0))
        ' do something with AnyColumn
        i = i + 1
    Next
If there's an answer, I'm sure it's easy and I'm missing something.

Any help gladly appreciated.