Excel VBA Find method to find cell row

[philrock]

I'm very much a noob at VBA for Excel. I'm trying to write a function that will return the row number of a cell where certain data is located. In the code below, the data is all located in column B, and I'm trying to find the row number of the cell containing the string "d1".

Function d1row()
d1row = Range("B:B").Find(What:="d1", _
After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row
End Function

All I get is the #VALUE! error in the worksheet cell where I enter the function. I've tried many variations on the Find parameters, but always the same result.

What am I doing wrong?

 

[SkipVought]

hi,

Paste this in a MODULE...

Function d1row()
      d1row = ActiveSheet.Range("B:B").Find("d1").Row
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[philrock]

Still get the #VALUE! error message.

Since Find returns a Range, do I need to do something before asking for a Row? Like; Cell? or Address?

 

[macropod]

Hi philrock,

If all you're after is the first match, why not use Excel's MATCH function? For example:
=MATCH("d1",B:B,0)

As a vba function, this could look like:

Function FindRow(myStr As String, myRng As String) As Long
FindRow = Application.WorksheetFunction.Match("d1", ActiveSheet.Range(myRng), 0)
End Function

and the equivalen worksheet formula would be:
=FindRow("d1","B:B")
Since you already know the column, it seems a bit pointless to return the full address.

Cheers

[MS MVP - Word]

 

[SkipVought]

Do you ACTUALLY have a string d1 in column B?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[philrock]

I already tried MATCH, and it worked fine. The worksheet has a lot of math, a lot of cells, and will have a lot of functions like this. I would like to avoid WorksheetFunctions in the code, in the interest of speed.

Yes there is a cell with "d1" in it. I'm wondering if the code might be confusing the text string "d1" with cell address "d1". I'll try text that cannot be a cell address and post the results.

 

[philrock]

Tried:

Function hoursrow()
hoursrow = ActiveSheet.Range("B:B").Find("hours").Row
End Function

Still get the #VALUE! error message.


btw, how do you get that nice little CODE window in a post?

 

[philrock]

btw, I'm using Excel 97. Is it too old to have the required functionality?

I noticed that if I hand type the part of the line after "hours", I do not get a list of suggestions right after I type ).

 

[SkipVought]

btw, how do you get that nice little CODE window in a post?

Search for TGML on this page. Open the link. Check out the CODE feature.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi philrock,

I already tried MATCH, and it worked fine. ... I would like to avoid WorksheetFunctions in the code, in the interest of speed.

The MATCH function will be significantly faster than a roll-your-own UDF equivalent on the worksheet. So, unless you're doing the search in code, MATCH is the way to go. If you need to do the find in code, then a function like:

Function FindCell(myStr As String, myRng As String, bMatch As Boolean) As String
Dim oCel As Range
Set oCel = ActiveSheet.Range(myRng).Find(myStr, LookIn:=xlValues, MatchCase:=bMatch)
If oCel Is Nothing Then
  FindCell = "#N/A"
Else
  FindCell = oCel.Address
End If
End Function

works fine. As you can see, I've added some extra functionality for case matching (bMatch = 1 to match case, 0 to ignore case) and the code now returns the actual address. vba offers a lot more parameters to play with if you need them - see the vba Help for details.

You can test the function with:

Sub Test()
MsgBox FindCell("Hours", "A:B", 0)
End Sub

[MS MVP - Word]

 

[philrock]

macropod,

Thanks for the ideas -

I'm doing the search in code, so I'm hoping to use something like your FindCell function. I tried it exactly as above, and still got the #VALUE! error.

I also tried the Test Sub. It gave a message box saying "That name is not valid.

 

[macropod]

Hi philrock,

The fact that no-one's code seems to work for you suggests there's something wrong with your system, or you're either:
. not inputting/calling the code correctly
. modifying the code
(which is the only way you could get the #VALUE! error).

The FindCell function in my last post definitely works when called from the Test sub - I verified it by simply copying & pasting both lots of code from that post and pasting them into a regular code module in a new workbook.

As I also said, using a UDF on a worksheet is less efficient than an inbuilt Excel function, so your efforts in that regard are misguided - the UDF will slow down the workbook's performance compared to a formula using the MATCH fucntion. In any event, a UDF based on the vba Find function won't work this way. Instead, you could use something like:

Function FindCell(myStr As String, myRng As String, bMatch As Boolean) As String
Dim oCel As Range
FindCell = "#N/A"
For Each oCel In ActiveSheet.Range(myRng)
  If InStr(1, oCel, myStr, bMatch ^ 2) > 0 Then
    FindCell = oCel.Address
    Exit For
  End If
Next
End Function

Cheers

[MS MVP - Word]

 

[xlbo]

I'm gonna go ahead and assume you're using your function in a cell on the spreadsheet

If that is the case, what you have found is that the FIND function does not work in this context

When calling a function from a cell =d1Row() as per your 1st example, the FIND function simply won't work - it will if you call it from code (try running the function from the VB Editor to prove this)

To do this in a spreadsheet cell, you must use the MATCH function as per previous suggestions

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi xlbo,

I'm gonna go ahead and assume you're using your function in a cell on the spreadsheet

I'm pretty sure that's the case - philrock originally said:

All I get is the #VALUE! error in the worksheet cell where I enter the function

As for your observation:

the FIND function does not work in this context

I think that confirms what I said:

In any event, a UDF based on the vba Find function won't work this way.

Cheers

[MS MVP - Word]

 

[xlbo]

Apologies macropod - didn't spot your text stating that the find function wouldn't work ion a UDF

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi xlbo,

That's OK, it's good to have independent confirmation anyway!

Cheers

[MS MVP - Word]

 

[philrock]

Guys,

Thanks very much for your patience - I really appreciate it.

macropod,

I cut and pasted your code into a new module in a new worksheet, without mods.
Then I put [tt]"Hours"[/tt] in cell B19 on the worksheet (without the " marks).
Then I put [tt]=findcell("Hours", "B1:B20", 0)[/tt] in cell D12 on the worksheet. This returned [tt]#N/A.[/tt]
Then I erased [tt]"Hours"[/tt] from cell B19, and re-entered the formula in cell D12. Still returned [tt]#N/A.[/tt]
Seems like we're making some progess beyond the [tt]#VALUE![/tt] error.

I mentioned above that I'm on pretty old software:
I clicked on About Visual Basic, System Info and got:
vba332 version 3.0.7019, 11/17/1996
vba3en version 5.00.3409, 11/17/1996

Is it possible that versions this old just don't have the needed functionality?

If I'm interpreting some of the statements above correctly, I'm getting conflicting messages; xlbo, you said the Find method does not work in this context. macropod, you said your code using the Find method works on your computer. Just to clarify - my intent is to use the Find method in a UDF.

Whenever I put text in a cell, Excel adds a leading ' . My understanding is that this is just a display formatting character, and will have no effect on what we are trying to accomplish. Is this correct?

macropod, your code with a For loop seems to work - I just have to extract the row number from the address that is returned. I'm thinking if we can get [tt]Find[/tt] to work, it will be faster than the loop - right?

 

[SkipVought]

Can you, ON THE SHEET, Find Hours?

If you can, then macro record the process.

What do you get?

Skip,
sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[philrock]

SkipVought,

Yes, I can use the conventional Excel way of finding "Hours" on the worksheet.

I recorded the macro. Here's the resulting code:

Sub findhours()
'
' findhours Macro
' Macro recorded 5/22/2009 by philrock
'
' Keyboard Shortcut: Ctrl+h
'
    Cells.Find(What:="hours", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
End Sub

The macro works just like the Excel finder for finding "hours."

Seems like it should be a piece of cake to massage this a bit and get a function that returns the cell row, but it's escaping me.

 

[macropod]

Hi philrock,

I've been testing this with Excel 2000, which isn't all that far removed from Excel 97. I doub there's been any change in this area between those versions.

Replicating the steps you took:
1. I cut and pasted the last 'FindCell' function I posted into a new module in a new worksheet, without mods.
2. I put 'Hours' in B19 on the worksheet (unquoted).
3. I put =findcell("Hours", "B1:B20", 0) in cell D12 on the worksheet. This returned $B$19.
4. I erased "Hours" from cell B19, and re-entered the formula in cell D12. This returned #N/A.

If I use the first 'FindCell' function I posted this way, step 3 returns #N/A. With both functions, the Test sub I posted returns $B$19 when 'Hours' is in B19. As xlbo and I have both said, the vba Find function does not work when used in a UDF formula like =findcell("Hours", "B1:B20", 0). Likewise, a simple UDF like this will always be less efficient than the equivalent native Excel function (in this case, MATCH). On that note, I don't propose to invest any more time in this.

Cheers

[MS MVP - Word]