VBA: Find column number after find

kweaver

Well-known Member
Joined
May 12, 2009
Messages
2,934
Office Version
  1. 365
  2. 2010
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.
 
Last edited:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Never mind...I realized I needed to use Application.Match

Testing now.
 
Upvote 0
Maybe
Code:
Dim x As Variant
x = Application.Match(Sheets("sheet1").Cells(i, 1), Sheets("sheet2").Range("$G$4:$Z$4"), 0) + 6
 
Last edited:
Upvote 0
Here is some untested code for you to try

Code:
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
 
Upvote 0
I did the same line as Fluff once I realized that MATCH needed to be Application.Match DUH! Should have known better.
 
Upvote 0
Glad you sorted it & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top