VBA - Find cell column of first match and second match

[dancran]

Hey MrExcel community,

This should be a pretty quick question.

I am entering a value in B1. The value entered in B1 could be in multiple places in Row 4.

To start, I just want to return the column number of the first instance where the value in B1 equals a value in Row 4 in VBA.

Is there a way to do this by setting the range to search through so that I can control which instance it returns? Any help in writing out this logic in VBA would be really helpful.

Dan

 

[Gary McMaster]

Hi Dan,

Try this:

Dim oFound As Range

Set oFound = ActiveSheet.Range("4:4").Find(ActiveSheet.Range("B2").Value, MatchCase:=False, lookat:=xlWhole)

If Not oFound Is Nothing Then
    MsgBox "Match in coulmn " & oFound.Column
End If

Gary

 

[dancran]

Hey Gary,

Thanks for the reply. Quick question. What is an easy way to debug and display what oFound is returning for the range to make sure it will work as I want?

Thanks,
Dan

 

[Gary McMaster]

What is an easy way to debug and display what oFound is returning

Either

Debug.Print oFound.Address

or

MsgBox oFound.Address

Gary

 

[dancran]

Gary,

Your statement is returning the correct instance I need so thank you.

The issue I am running into now though is how to utilize this result. It returns a cell location I.E. - $J$4 or whatever I am searching for.

The problem I am running into is that I need to loop until I get to that column. Is there a way for the statement to return only the column or the number value of the column I.E. - Instead of E return 5 etc?

This would allow me to loop until I get to that column which is my end goal. Any ideas?

Thanks for all your help so far!
Dan

 

[lokiluke]

use the .column , this will return the column number of the object.

e.g.

For x=1 To oFound.Column
'do stuff here
Next x

 

[dancran]

Thanks! Didn't realize it was as simple as that..

After further testing, oFound returns the location of the LAST match in the range if there are multiple matches. How would I get it to return the first one besides renaming range because that will be dynamic?

 

[Gary McMaster]

Check VBA help on the "Find Method". There are a lot of command modifiers such as MatchCase:=False & lookat:=xlWhole (used in sample) that allow you to specify search direction and starting point among other things. Maybe one of those modifiers will force it to behave the way you want it to.

Here is another sample that doesn't use "Find" but it's no where near as versatile as Find.

Gary

Public Sub Test()

Dim oCell As Range
Dim oSearch As Range
Dim vFind As Variant
Dim lColumn As Long

vFind = ActiveSheet.Range("B1").Value

Set oSearch = ActiveSheet.Range("4:4")

lColumn = 0
For Each oCell In oSearch
    If oCell.Value = vFind Then
        lColumn = oCell.Column
        Exit For
    End If
Next oCell

If lColumn <> 0 Then
    MsgBox "Column: " & lColumn
Else
    MsgBox "Nothing found"
End If

End Sub