Find value from sheet 1 on sheet 2 and copy to an offset from there

[L. Howard Kittle]

Hello Excel Experts and Users,
Excel 2002

Code to do the following:

Sheet 1 B3 = 123
Sheet 1 B5 = "What"
Sheet 1 B6 = "Ever"

On Sheet 2 find 123 in column D (Anywhere in D)
On sheet 2 "What" goes in same row in column K
On sheet 2 "Ever" goes in same row in column L

I can make it work quite easily if 123 is the last value in Sheet 2, column
D, but that may not be the case. Have floundered with Find and Offset and
Resize but am upside down! Damn, I know I have done this and cannot find
examples in my archives or Google.

Thanks,
Regards,
Howard

 

[NickHK]

Howard,
Try this:

Private Sub CommandButton3_Click()
Dim FoundCell As Range
Dim SourceWS As Worksheet

Const COLUMN_K As Long = 11
Const COLUMN_L As Long = 12

Set SourceWS = Worksheets(1)

With Worksheets(2)
Set FoundCell = .Range("D").Find(SourceWS.Range("B3").Value, ,
xlValues)
If Not FoundCell Is Nothing Then
.Cells(FoundCell.Row, COLUMN_K).Value = SourceWS.Range("B5")
.Cells(FoundCell.Row, COLUMN_L).Value = SourceWS.Range("B6")
End If
End With

End Sub

NickHK

 

[L. Howard Kittle]

Thanks NickHK,

I will give it a go. I probably don't need the Command Button Click...??
Use a Forms button maybe?

Off to try to make it work. I'm sure it is an exact answer to what I asked,
but still a bit puzzeling to me.

Thank you,
...Film at 11

Regards,
Howard

 

[L. Howard Kittle]

Hi NickHK,

Code works perfect, thanks a ton!

Regards,
Howard