search header information

DebtAcquisitions

New Member
Joined
May 20, 2015
Messages
21
Is it possible to search row 1 (headers) for a value defined by a table from another sheet.

example row1 = One, Two, Three, four

sheet 2, column A:A = five, six, one, seven.

if sheet 1, row 1 contains any of the values listed in sheet 2!A:A, I need to have an output of true, or msgbox or anything......

I was able to do this by defining exactly what value I was looking for, I need to do this by defining what table to look at for the value.
(I hope I am not over complicating this)

Here is a sample of what I was able to get working:

FName = Workbooks("IntChk.xlsm").Worksheets("Data").Range("B3")

Set rngFound = Worksheets("File").Rows(1).Find(What:=FName, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)



Note: I need "FName" to be a column or range of values as apposed to a single cell.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
I'd use a CountIf statement to find out if there are any matches for each of the header values in row 1 of first sheet within the first column of the second sheet.

Code:
Dim cel As Range

For Each cel In Range(Cells(1, 1).Address, Cells(1, Columns.Count).End(xlToLeft).Address)
    With WorksheetFunction
        If .CountIf(Sheet2.Columns(1), cel.Value) > 0 Then
            'highlight yellow when match found
            cel.Interior.ColorIndex = 6
        End If
    End With
Next cel
 
Upvote 0
Guess I should share my results just in case someone else needs something like this.

Dim i As Integer

For i = 1 To 1
For j = 1 To 3
MsgBox "Cell Value = " & Cells(j) & vbNewLine & "Column Number = " & j
For Each c In Workbooks("IntChk.xlsm").Worksheets("Data").Range("A1:A50")
If c.Value = Cells(j) Then
MsgBox "Match"
Match = "True"
End If
Next c
Next j

If Match = "True" Then
MsgBox "Yes, True!"
Else:
MsgBox "not true ;("

End If

Next i
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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