Macro: Find Value and paste data

[woodm872]

Question...
I am trying to write a macro that takes the Value in B1, finds B1 in row 5 and pulls in the coresponding data in E1:F3. I would just put in an index match match but the data prior to the current week has to be able to stay without getting deleted. Any help is greatly appreciated.



[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]week[/TD]
[TD]34[/TD]
[TD][/TD]
[TD]data[/TD]
[TD]abc[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]def[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ghi[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]31[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD]34[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]abc[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]def[/TD]
[TD]16[/TD]
[TD]14[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]ghi[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[tonyyy]

woodm872,

Welcome to the Board.

You might consider the following...

Sub GetData()
Dim Col As Long
On Error Resume Next
Col = Rows(5).Find(What:=Range("B1").Value, After:=Cells(5, 1), LookIn:=xlFormulas, _
    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0
If Col > 0 Then
    Range("E1:E3").Value = Range("A6:A8").Value
    Range("F1:F3").Value = Range(Cells(6, Col), Cells(8, Col)).Value
End If
End Sub

Cheers,

tonyyy