Load into textbox two values from customer on worksheet

[ipbr21054]

Hi,
I have a customers name in cell G13 example TOM JONES 001 on worksheet INV
I open a userform & i need 2 TextBoxes to be populated with values from my worksheet for TOM JONES 001

It should work like this.
On worksheet INV in cell G13 take note of customers name.
Look for this customers name on worksheet DATABASE in column A
Once found the values needed are in column C for TextBox3 & column K for TextBox2

Thanks please advise.

I was using the below but the name in question works if on row 6

Private Sub UserForm_Initialize()

    Me.StartUpPosition = 0
    Me.Top = Application.Top + 210  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 350 ' LEFT / RIGHT OF SCREEN

    TextBox2.Value = Sheets("DATABASE").Range("K6").Value
    TextBox3.Value = Sheets("DATABASE").Range("C6").Value

End Sub

 

[ipbr21054]

Not sure if im heading the right way but i have this below which will find the value in cell G13 on the worksheet in question.

Not sure how i edit / add to it so TextBox2 & TextBox3 get the values needed.
Basically Customers name in cell then offset by 3 for TextBox3 & offset by 11 for TextBox2 ?

  Dim findString As String
  Dim rng As Range
  findString = Range("G13").Value
    With Sheets("DATABASE").Range("A:A")
    Application.EnableEvents = False
   Set rng = .Find(What:=findString, After:=.Cells(.Cells.count), LookIn:=xlValues, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not rng Is Nothing Then
      Application.Goto rng, True
     Else
      MsgBox "CUSTOMER NOT FOUND"
    End If
    Application.EnableEvents = True
  End With

I understand my code takes me to the cell & its not needed but im just trying to work it through

 

[AlphaFrog]

Private Sub UserForm_Initialize()

    Dim Customer  As String
    Dim Found     As Range

    Me.StartUpPosition = 0
    Me.Top = Application.Top + 210  ' MARGIN FROM TOP OF SCREEN
    Me.Left = Application.Left + Application.Width - Me.Width - 350    ' LEFT / RIGHT OF SCREEN

    Customer = Sheets("INV").Range("G13").Value

    With Sheets("DATABASE")

        Set Found = .Columns("A").Find(Customer, , xlValues, xlWhole, 0, 0, 0)

        If Not Found Is Nothing Then

            TextBox2.Value = .Range("K" & Found.Row).Value
            TextBox3.Value = .Range("C" & Found.Row).Value

        Else

            MsgBox Customer, vbExclamation, "No Match Found"

        End If
    End With
End Sub

 

[ipbr21054]

Hi,
Straight away i see Subscript out of range.
This line is then shown in yellow.

Set Found = .Columns("A").Find(Customer, , xlValues, xlWhole, 0, 0, 0)

 

[AlphaFrog]

Set Found = .Columns("A").Find(Customer, , xlValues, xlWhole, 1, 1, 0)

 

[ipbr21054]

Perfect thanks for the help

 

[AlphaFrog]