copy and paste via a sub to look up data

[iceblocks]

Hi all,
I am trying to write a sub that will find data from a list and then paste them to another sheet. A simplified spreadsheet is attached to help explain my example. I thought about doing this with a vlookup formula but then vlookup won’t allow user to overwrite the data where necessary. Hence a macro is probably the best way ahead.
Data are listed in the tab called ‘Data’ by their respective id number. So I need a sub to copy data from the ‘Data’ Sheet when an id number is entered into cell B2 of the Report tab. For example, copy the rating for client 101 to B3 and copy comment to B4 in the ‘Report’ sheet.
Would be grateful of your assistance. Many thanks.
iceblocks

 

[Nebu]

Hi:

There is no need for the formula to overwrite. The Vlookup formula itself is dynamic in the sense the values pulled in B3 and B4 is depended on the entry you make in cell B2.In short, the values in B3 and B4 will dynamically change as per the value you enter in B2.

Thanks

 

[iceblocks]

Thanks Nebu,
But my boss prefers a macro because after the report is generated, he want person to view the comments and overwrite where appropriate. And the vlookup function does allow for his specific needs.

Any assistance on writing this macro is greatly appreciated.

 

[Nebu]

Hi:

I am tied up today.Will try to put something together tonight after office.

Thanks

 

[wudixin96]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim findRange As Range
    If Target.Address = "$B$2" Then
        With Sheets("Data")
            Set findRange = .Range("A:A").Find(Target.Value, lookat:=xlWhole)
            If Not findRange Is Nothing Then
              Target.Offset(1).Value = findRange.Offset(0, 1).Value
              Target.Offset(2).Value = findRange.Offset(0, 2).Value
            Else
                Target.Offset(1).Resize(2).ClearContents
                MsgBox "Not Found !"
            End If
        End With
    End If
End Sub

 

[iceblocks]

Thank you so much wudixin96. Much appreciated.
Thanks Nebu for all your help.