VBA: Find column number after find

[kweaver]

I'm looping through rows of a sheet and want to find the column number when Sheets("Data").Cells(i,1) matches a value in another sheet in a range.
For example, what is the matching column number when Sheets("Data").Cells(i, 1) matches a value in Sheets("Output").Range("$G$4:$Z$4")

The match could be column 7, 8, 9, etc. and there will be a match and will be only once.

 

[kweaver]

Never mind...I realized I needed to use Application.Match

Testing now.

 

[Fluff]

Maybe

Dim x As Variant
x = Application.Match(Sheets("sheet1").Cells(i, 1), Sheets("sheet2").Range("$G$4:$Z$4"), 0) + 6

 

[alansidman]

Here is some untested code for you to try

Option Explicit


Sub AMS67()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Data")
    Set s2 = Sheets("Output")
    Dim i As Long, lr As Long, j As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For j = 8 To 26
            If s1.Cells(i, 1) = s2.Cells(4, j) Then
                MsgBox ("Found in Column " & j)
            End If
        Next j
    Next i




End Sub

 

[kweaver]

I did the same line as Fluff once I realized that MATCH needed to be Application.Match DUH! Should have known better.

 

[Fluff]

Glad you sorted it & thanks for the feedback