Find column number of cell containing numeric value using excel VBA

Question

In my excel sheet, Value of cell A1 is 1000 whereas that of cell B1 is 10000.

I am trying to find column no. of a cell that contains "1000".

Column no. output I want is 1 (i.e. cell A1) but I am getting output as 2 with below code (referring to value 10000 in cell B1). On the other hand in cell B1 if I replace 10000 by say 10100, I get output of below code as 1.

May I know what change do I need to do in code to get desired output?

*

Sub Find_Number()
Dim Find As Range
Set Find = Range(Cells(1, 1), Cells(1, 5)).Find("1000")
Debug.Print Find.Column
End Sub

*

Specify the other parameters of Range.Find: After, LookIn, LookAt, etc.. After should be the last cell. — BigBen
– BigBen, Commented Mar 31, 2020 at 16:11
If you want to find exactly 1000, then Application.Match is another option. — BigBen
– BigBen, Commented Mar 31, 2020 at 16:15
LookIn:= xlWhole. I would rename Find to Found to avoid using a key word as variable. Also makes statements like these intuitive: If Not Found is Nothing Then Debug.Print Found.Column — urdearboy
– urdearboy, Commented Mar 31, 2020 at 16:16
Thank you @urdearboy for your valuable help & suggestion. Summary is I have to use LookAt:=xlWhole if I use Range.Find or else better use Application.Match — Puntal
– Puntal, Commented Mar 31, 2020 at 16:37

SJR · Accepted Answer · 2020-03-31 16:24:45Z

2

Try this. When using Find always advisable to specify the parameters as they may have unexpected settings. By specifying after as the last cell in the range the search will start in the first cell rather than skipping it.

And check you've found something before proceeding to avoid errors.

An alternative approach is to use Match which will work fine on a 1D range.

(Also I don't like using Find as a variable name even if it is allowed.)

Sub Find_Number()

Dim rFind As Range

With Range(Cells(1, 1), Cells(1, 5))
    Set rFind = .Find(what:=1000, after:=.Cells(.Cells.Count), LookAt:=xlPart, _
                     SearchDirection:=xlNext, MatchCase:=False) 'might want to specify whole rather than part
    If Not rFind Is Nothing Then Debug.Print rFind.Column 'avoid error if not found
End With

'alternative
'Dim v As Variant

'v = Application.Match(1000, Range(Cells(1, 1), Cells(1, 5)), 0)
'If IsNumeric(v) Then Debug.Print v

End Sub

edited Mar 31, 2020 at 16:24

answered Mar 31, 2020 at 16:19

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Puntal Over a year ago

Thank you so much for this. Just one query. 10000 has 1000 in it, that why I got wrong column no. as output since I didn't mention other parameters. So am I expected to mention LookAt:=xlWhole to exactly match my search? (Alternate answer is also awesome. Thank you so much.)

SJR Over a year ago

Yes, that's it exactly.

Puntal Over a year ago

Noted your suggestion of not using Find as variable name. Thanks once again for support.

Collectives™ on Stack Overflow

Find column number of cell containing numeric value using excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related