vba code to search through multiple tables

[fri3nd5]

Hi guys!
On sheet1 i have 5 different single column tables with different header (test1, test2, ... test5). All tables have header on row no. 2. Tables are separated one of the other by a blank column. (ex: 1st table is on column A, 2nd table is on column C, ......)
On sheet2 cell c5 i type the header of one of those tables (ex: test3).
I need to find a vba code to search through all headers from sheet1 and find header test3. After it finds it, to indicate the no of column where it is situated (in this example should return column E)

if it was a single table with multiple columns the code below is working. Because there are multiple table the code is not working.

can anybody help me please?

Private Sub CommandButton1_Click()

Dim found As Range
Dim mcins As String
Dim mcdata, line, column As Integer


'FIND MC
mcins = Sheet2.Cells(5, 3)
Set found = Sheet1.Range("2:2").Find(What:=mcins)
LookIn = xlValues
LookAt = xlValues
If found Is Nothing Then
    MsgBox "Select The Correct Main Contractor"
    Exit Sub
Else
    mcdata = found.column

End If

End Sub

 

[Monty]

Hello fri3nd5

Can i request to upload sample file for quick solution.


Thanks
Monty!

 

[fri3nd5]

I HAVE SOLVE IT !!!
that code is good ... the only problem was that the command button was on sheet2 and ms excel didn't like that ... so i added another commandbutton on sheet1 (the same sheet where the tables are) and i triggered it by command button from sheet2 (where i insert the text).
It is working perfectly
Thanks Monty for willing to help.

 

[Monty]

My Pleasure.