Return row

[Guest]

Hi,

Using VBA, I need to take the value of one cell, find it in a range of cells
and return the row number it is found on. Is there a function that will do
this? I thought if might be Match but it is erroring out as a type mismatch.
Isn't the row number treated as a number?

Dim PoolChoice as Long

PoolChoice = Application.Match(Tablespg.Cells(Choice, 24),
Tablespg.Range("CAMPoolTypes"), 0)

 

[Guest]

Karen, something like this should work:
(R1 is the cell containing the value you want to find, R2 is the range where
you want to find the value.)
Dim R1 As Range
Dim R2 As Range
Dim MyRow As Long
Set R1 = Range("A1")
Set R2 = Range("D1:F10")
MyRow = R2.Find(R1.Value).Row

 

[Guest]

Thank you, Charles!

 

[Guest]

Hi again,

I've hit a snag. What if one of the values contains the value from another
cell. For example, I have "Trash" as a pooltype and "Common Area Trash" as a
pooltype. It is picking up the first one it comes to, i.e. "Common Area
Trash" when it is looking for "Trash". How would I get around this?

 

[Guest]

MyRow = R2.Find(R1,LookAt:=XlWhole).Row

Let me know if you need anything else

 

[Guest]

Thanks, Charles!