Find value then offset, to set variables

[Guest]

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...

 

[Guest]

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

 

[Guest]

Thank you for the reply Tom, is it possible to do it without selecting the
cell using VBA ??

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thank you so much Jim, exactly what i was looking for.... :0)

 

[Guest]

Did so, thanks Jim, have a great day/evening