Range.Find() Function Returning Value instead of Range

[Enzo Jono]

Hello,
I have encountered an issue when trying out the Range.Find() function in one of my workbooks: the function returns the very value I gave it instead of the cell where it found the value.

The above piece of vba code is what I have been trying to do (to get myself familiar with the Find function), but when running it I get the following error:

and when I click Debug, it highlights my row function (as can be seen in the first screenshots), and when I hover over the a_cell it shows me a_cell = "V0", which is the argument I gave it. Isn't the find function supposed to return the range in which it found the argument or Nothing if it doesnt find it?? How am I getting a string out of the Find function?

 

[jasonb75]

For the highlighted line, it should just be

a_row = a_cell.Row

a_cell is already set as a range so you don't use the Range() command with it here.

Also, it would be better to use Dim a_row As Long Using Integer will cause an error if the row is too far down the column.

How am I getting a string out of the Find function?

When you hover you get the current value of the variable, for a range it is the content of the cell that is set to the range, not the location of the cell.

Another thing to note it that Find uses the same parameters as Ctrl f in excel, if you have changed any of the options there then vba Find will use the last settings from that as default. For this reason it is recommended to use all arguments of Find in your code rather than just the ones that you think you need.

Range.Find method (Excel)

Office VBA reference topic

learn.microsoft.com

 

[Enzo Jono]

For the highlighted line, it should just be

a_row = a_cell.Row

a_cell is already set as a range so you don't use the Range() command with it here.

Also, it would be better to use Dim a_row As Long Using Integer will cause an error if the row is too far down the column.

When you hover you get the current value of the variable, for a range it is the content of the cell that is set to the range, not the location of the cell.

Another thing to note it that Find uses the same parameters as Ctrl f in excel, if you have changed any of the options there then vba Find will use the last settings from that as default. For this reason it is recommended to use all arguments of Find in your code rather than just the ones that you think you need.

Range.Find method (Excel)

Office VBA reference topic

learn.microsoft.com

Thank you for the a_row= a_cell.Row bit, it solved the issue somehow. So Excel's tooltip will just show the value instead of the range it appears...

 

[jasonb75]

So Excel's tooltip will just show the value instead of the range it appears...

That is correct, it you want to see where it is found then you could use a_cell.Address(0, 0) with a string variable.