| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return date if in range, else return blank | Excel Worksheet Functions | |||
| Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
| LOOKUP and return the column heading for IF/THEN return for False | Excel Discussion (Misc queries) | |||
| check if reference exists, then return its value or return 0 | Excel Worksheet Functions | |||
| VBA Syntax for VLOOKUP to return array of return values | Excel Programming | |||
Hybrid Mode
