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.

 

[Fluff]

Will the value be found in a specific column, or could it be anywhere on the sheet?

 

[Mick Peters]

Will the value be found in a specific column, or could it be anywhere on the sheet?

Yes the look up value is always in column D and the result I am looking to display in the new text box is in F.
As I said though the value can repeat and I am looking for the last occurrence.
Thank you for any help you can give.

 

[Fluff]

Ok, how about

Private Sub TxtOld_AfterUpdate()
   Dim Old As String
   Dim FoundRange As Range
   
   Old = TxtOld.Value
      
   Set FoundRange = Sheets("Sheet1").Range("D:D").Find(Old, , xlFormulas, xlWhole, , , False, , False)
   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
   Set FoundRange = Nothing
   Set FoundRange = Sheets("Sheet2").Range("D:D").Find(Old, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)
   If FoundRange Is Nothing Then
      Txtbx2 = "Not found"
   Else
      Txtbx2 = FoundRange.Offset(, 2).Value
   End If
End Sub

 

[Mick Peters]

Hi Fluff,
I added the txt box 2 on to the form and replaced my VBA with yours but I get a debug failure on line,
"Set FoundRange = Sheets("Sheet2").Range("D:D").Find(Old, , xlFormulas, xlWhole, xlByRows, xlPrevious, False, , False)"
I m not sure if I was clear this is an as well as not an instead of.
Your code at the bottom looks to be using the same found range as that at the top should it not be looking for a different one EG Found range 2 or does the found range = nothing clear the old one out?

 

[Fluff]

What is the error number & message?

 

[Mick Peters]

Hi Fluff
The error is
Run-Time error '9':
Script out of range.
Thank you ,
Mick.

 

[Fluff]

That means you do not have a sheet called Sheet2, you need to change it to the actual name of the sheet.

 

[Mick Peters]

Fluff, Spot on I have renamed sheet 2 to "Enter details". The code worked but returned 2 rows over not 1 so I have changed that "Txtbx2 = FoundRange.Offset(, 1).Value" and I can make it work now with a small exception and I am not sure if this is a new question or a follow on, Please advise.
In the Enter details sheet when the user is entering the information all of the entries start WRF so I get excel to add that in as a prefix and the user just keys 4 digit. On the user form I do the same but the code does not pick up that WRF is on in the cell in column D even though it has picked it up from the user form.
So on my user form it opens with WRF and the user enters 1234 the code searched Column D which reads WRF1234 but is in fact 1234 to Excel as the WRF is a prefix and does not find a match.
To test and prove your code worked I removed from the form the WRF and searched only for the numbers and it worked.
I wish to keep the auto prefix how can I make the code read what it displayed not what is stored please?
Thank you ,
Mick.

 

[Fluff]

Is this the same for both sheets?