VBA Help

[Jennifer Van]

Hi
I had help with this VBA and it was working until I added columns and tried to fix it up myself but seemed to have mucked it up.

My worksheet "Loan Request Return" puts data on a row and in column U an identifying Unique ID Number is placed and in columns AA, AB,AC is placed current status of the equipment.

I need this information from rows AA,AB,AC to be placed into worksheet "Equipment Library" - however not just on a blank row - finding the matching identifying Unique ID Number from column U in the "Loan Request Return" worksheet.

The Unique ID Number is located in column D of the "Equipment Library" worksheet and the columns AA,AB,AC need to go into columns V,W,X on the "Equipment Library" worksheet.

Can someone help adjust this code please?

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Columns(21)) Is Nothing And Target.Row >= 5 Then

Dim Sval As Range, wsEL As Worksheet, wsLRR As Worksheet

Set wsLRR = Sheets("Loan Request Return")
Set wsEL = Sheets("Equipment Library")
Set Sval = wsEL.Columns("C:C").Find(Target.Value)

If Target.Value = Sval.Value Then
Target.Offset(, 5).Resize(, 3).Copy Sval.Offset(, 17)
End If
End If

End Sub

 

[Anthony47]

I seem you need to modify the two lines marked ***

Set Sval = wsEL.Columns("D:D").Find(Target.Value)           ' ***

If Target.Value = Sval.Value Then
    Target.Offset(, 5).Resize(, 3).Copy Sval.Offset(, 23)   ' ***
Else
    MsgBox ("ID " & Target.Value & " NOT FOUND on Sheets(Equipment Library)!")   '???
End If

In case it's possible for the user to type a wrong ID and it's better to report this case then I recommend you also add an "Else" condition (the line marked ???)

 

[igold]

I got a little turned around in your description, but does this do anything for you...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Columns(21)) Is Nothing And Target.Row >= 5 Then
    
        Dim Sval As Range, wsEL As Worksheet, wsLRR As Worksheet
    
        Set wsLRR = Sheets("Loan Request Return")
        Set wsEL = Sheets("Equipment Library")
        Set Sval = wsEL.Columns("D:D").Find(Target.Value)
        If Target.Value = Sval.Value Then
            Target.Offset(, 6).Resize(, 3).Copy Sval.Offset(, 18)
        End If
    End If

End Sub

 

[Anthony47]

@igold
I think we both got confused by the cryptic description: I forgot to correct Offset(, 5) to Offset(, 6) and probably you forgot updating Offset(, 17) to Offset(, 23)

 

[igold]

@Anthony47
I feel better that it was not just me who got lost.
I updated Offset(,17) to Offset(,18) and it placed correctly in Columns V,W,X... If that was the intended outcome...

 

[Anthony47]

Gaahhhahah
Yes, I think Offset(,18) is the right one!

 

[igold]

There is only one opinion left that counts...