Return row
 

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)
--
Thanks for your help.
Karen53

Return row
 

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

--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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)
--
Thanks for your help.
Karen53

Return row
 

Thank you, Charles!
--
Thanks for your help.
Karen53


"Charles Chickering" wrote:

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

--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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)
--
Thanks for your help.
Karen53

Return row
 

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?
--
Thanks for your help.
Karen53


"Charles Chickering" wrote:

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

--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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)
--
Thanks for your help.
Karen53

Return row
 

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

Let me know if you need anything else
--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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?
--
Thanks for your help.
Karen53


"Charles Chickering" wrote:

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

--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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)
--
Thanks for your help.
Karen53

Return row
 

Thanks, Charles!
--
Thanks for your help.
Karen53


"Charles Chickering" wrote:

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

Let me know if you need anything else
--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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?
--
Thanks for your help.
Karen53


"Charles Chickering" wrote:

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

--
Charles Chickering

"A good example is twice the value of good advice."


"Karen53" wrote:

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)
--
Thanks for your help.
Karen53