| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find value then offset, to set variables
Good day, i am not a programmer but a dabbler and have a range called
"Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Turn on the macro recorder and use the find method (in the edit menu) to find
you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Thank you for the reply Tom, is it possible to do it without selecting the
cell using VBA ?? -- Les "Tom Ogilvy" wrote: Turn on the macro recorder and use the find method (in the edit menu) to find you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Give this a try...
Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rng.Offset(0, 1).Text strC = rng.Offset(0, 2).Text strD = rng.Offset(0, 3).Text End If End Sub -- HTH... Jim Thomlinson "Les" wrote: Thank you for the reply Tom, is it possible to do it without selecting the cell using VBA ?? -- Les "Tom Ogilvy" wrote: Turn on the macro recorder and use the find method (in the edit menu) to find you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Sorry... I messed up... change rng to rngFound...
Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rngFound.Offset(0, 1).Text strC = rngFound.Offset(0, 2).Text strD = rngFound.Offset(0, 3).Text End If -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Give this a try... Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rng.Offset(0, 1).Text strC = rng.Offset(0, 2).Text strD = rng.Offset(0, 3).Text End If End Sub -- HTH... Jim Thomlinson "Les" wrote: Thank you for the reply Tom, is it possible to do it without selecting the cell using VBA ?? -- Les "Tom Ogilvy" wrote: Turn on the macro recorder and use the find method (in the edit menu) to find you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Did so, thanks Jim, have a great day/evening
-- Les "Jim Thomlinson" wrote: Sorry... I messed up... change rng to rngFound... Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rngFound.Offset(0, 1).Text strC = rngFound.Offset(0, 2).Text strD = rngFound.Offset(0, 3).Text End If -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: Give this a try... Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rng.Offset(0, 1).Text strC = rng.Offset(0, 2).Text strD = rng.Offset(0, 3).Text End If End Sub -- HTH... Jim Thomlinson "Les" wrote: Thank you for the reply Tom, is it possible to do it without selecting the cell using VBA ?? -- Les "Tom Ogilvy" wrote: Turn on the macro recorder and use the find method (in the edit menu) to find you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find value then offset, to set variables
Thank you so much Jim, exactly what i was looking for.... :0)
-- Les "Jim Thomlinson" wrote: Give this a try... Sub FindStuff() Dim rngFound As Range Dim strB as string dim strC as string dim strD as String Set rngFound = Sheets("Sheet1").Columns("A").Find(What:="PU 03/07", _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) If rngFound Is Nothing Then MsgBox "Sorry... Not Found" Else strB = rng.Offset(0, 1).Text strC = rng.Offset(0, 2).Text strD = rng.Offset(0, 3).Text End If End Sub -- HTH... Jim Thomlinson "Les" wrote: Thank you for the reply Tom, is it possible to do it without selecting the cell using VBA ?? -- Les "Tom Ogilvy" wrote: Turn on the macro recorder and use the find method (in the edit menu) to find you target in A. once you have that code, then add this to it. sB = activecell.offset(0,1).Text sC = activecell.offset(0,2).Text sD = activeCell.offset(0,3).Text -- Regards, Tom Ogilvy "Les" wrote: Good day, i am not a programmer but a dabbler and have a range called "Projects" with the following in it: A B C D PU 03/07 90ZA0703 90IH0703 PU0703LCS PU 09/07 90ZA0709 90IH0709 PU0907LCS PU 03/08 90ZA0803 90IH0803 PU0803LCS PU 09/08 90ZA0810 90IH0810 PU0809LCS What i need to do is look up the value in "A" and then set 3 string variables with the values in "B", "C" & "D" to the right of the value found in "A". Any help with code would be very much appreciated... -- Les |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use of OFFSET and LOOKUP to find a value in a table | Excel Discussion (Misc queries) | |||
| Find Selected value to .Offset from | Excel Programming | |||
| Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
| Find then offset. | Excel Worksheet Functions | |||
| Using variables to make a date and using find method to find that. | Excel Programming | |||
Hybrid Mode
