|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 05:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com