User form Text field look up on 2 sheets.

[Mick Peters]

Hi I am trying to edit a piece of VBA that is behind my user form. The VBA takes the value that is keyed into Old and searches sheet 1 for that number from this it returns TxtIP and TextTanIP and then places them into the corresponding Txt field on the user form. All works well.

Private Sub TxtOld_AfterUpdate()
     
    Dim Old As String
    Dim FoundRange As Range
    'Dim Row As String
     
    Old = TxtOld.Value
    Worksheets("Sheet1").Activate
     
     
    Set FoundRange = Sheets("Sheet1").Cells.Find(what:=Old, LookIn:=xlFormulas, lookat:=xlWhole)
        If FoundRange Is Nothing Then
        TxtIP.Text = "Not found"
        TxtTranIP.Text = "Not found"
    Else
        TxtIP.Text = FoundRange.Offset(0, 1).Value
        TxtTranIP.Text = FoundRange.Offset(0, 1).Value
        TxtRow.Text = FoundRange.Row
        TxtMac.Text = FoundRange.Offset(0, 2).Value
        Record_Date = Now()
        
    End If
End Sub

What I now need to do as an extension is to use the same input OLD but to search Sheet 2 (simultaneously) to return a value and place it into another(New) text box on the form (I plan on naming something like Replacement). There may be more than 1 occurrence of the number on sheet 2 so I would prefer it to fetch the last one (Search bottom up not top down). The data I want will be in the cell next to the found item.
So the final result would be
Enter TextBox OLD
Return TxtIP From Sheet 1
Return TxtTranIP From Sheet 1
Return Replacment From Sheet 2

Any help would be appreciated.
Thank you,
Mick.

 

[Mick Peters]

No The first sheet is a list of the items so they were all filled in in full WRF1234 keyed in but the Enter details sheet and the User form are filling in day by day by the users so I am trying to reduce the keying as they refer to the items by only the last 4 digits it is easier for them.
So if the lookup on the Enter details sheet could just look for the last 4 digits of what is in TXTOld but the original look up still looked for the whole thing that would work but I am not sure how to do that.

 

[Fluff]

Ok add this line as shown

End If
Old = Mid(Old, 4) * 1
Set FoundRange = Nothing

You may need to remove the part in red, is the values in col D are text.

 

[Mick Peters]

No Sorry, Only the sheet Enter details has the prefix added by Excel. Sheet 1 is a fixed list of items that were keyed in long hand. Obviously they both use the same user form.
So if the line of code you added could change from,
Set FoundRange = Sheets("Enter details").Range("D:D").Find(Old, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)
To something like
Set FoundRange = Sheets("Enter details").Range("D:D").Find((Substr(Old,4,4), , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)
so where the form says WRF1234 it only looks for 1234 on the enter details sheet but looks for WRF1234 on sheet1.
Thank you again for you r help on this.

 

[Fluff]

If you add the line I showed, just before the Set FoundRange = Nothing line it will do what you want.

 

[Mick Peters]

Sorry I replied twice to the same post I though it had lost 1 but it was just next page.
So here is the rub It now works as expected for the Enter details page but it has stopped finding anything on the sheet 1 page. So can I rename with in the code and use the same text box but with 2 different names?

 

[Fluff]

What is the code you are currently using?

 

[Mick Peters]

Sorry I replied twice to the same post I though it had lost 1 but it was just next page.
So here is the rub It now works as expected for the Enter details page but it has stopped finding anything on the sheet 1 page. So can I rename with in the code and use the same text box but with 2 different names?
[/QUOT


I think I got it
So I put back the original code at the top and left the new code at the bottom and renamed the shortened filed name to Old 2.
I think this was my fault in a miscommunication that Sheet 1 and Enter details sheet are not in the same layout format , nothing you could have know. I could not have do this without your help , Many thanks,
Mick.

Private Sub TxtOld_AfterUpdate()
Dim Old As String
Dim OLD2 As String
Dim FoundRange As Range

Old = TxtOld.Value
OLD2 = Mid(Old, 4) * 1

Set FoundRange = Sheets("Sheet1").Cells.Find(what:=Old, LookIn:=xlFormulas, lookat:=xlWhole)
If FoundRange Is Nothing Then
TxtIP.Text = "Not found"
TxtTranIP.Text = "Not found"
Else
TxtIP.Text = FoundRange.Offset(0, 1).Value
TxtTranIP.Text = FoundRange.Offset(0, 1).Value
TxtRow.Text = FoundRange.Row
TxtMac.Text = FoundRange.Offset(0, 2).Value
Record_Date = Now()
End If
'OLD2 = Mid(Old, 4) * 1
Set FoundRange = Nothing
Set FoundRange = Sheets("Enter details").Range("D:D").Find(OLD2, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)
If FoundRange Is Nothing Then
Txtbx2 = "Not found"
Else
Txtbx2 = FoundRange.Offset(, 1).Value
End If
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback