VBA Help - Multiple Lookups and Input Boxes

[OJMcGee]

Hi,

Looking for help writing VBA that when run, will first of all ask for a name to be entered, which will be named as 'Input 1', it will then ask for a second name which will be named as 'Input 2'. It will then populate the notes on sheet 3 by looking up the references in the table to sheet 1 and then retrieving the correct note template from sheet 2.

Thanks

 

[sxhall]

Hi OJMcGee,

Try this as a starter. Asks for an inital name and then another name, inputs 1 & 2. have added in a third box to ask for the reference as I could not figure out what is was to use to look up for the template. I've added to ask for the reference number....

Sub GetTemplate()

Dim Input1 As String
Dim Input2 As String
Dim RefNum As Integer
Dim CompName As String
Dim UseTem As String
Dim Template As String
Dim Addrow As Integer

Input1 = InputBox("Type in the first name...")
Input2 = InputBox("Type in the first name...")

RefNum = InputBox("What is the reference number?")

With Sheet1
    CompName = .Columns("A:A").Find(What:=RefNum).Offset(0, 1).Value
End With

With Sheet2
    UseTem = .Columns("A:A").Find(What:=CompName).Offset(0, 1).Value
    Template = .Columns("E:E").Find(What:=UseTem).Offset(0, 1).Value
End With

With Sheet3
    If .Range("A2") <> "" Then
        Addrow = .Range("A1").End(xlDown).Offset(1, 0).Row
    Else
        Addrow = 2
    End If
    .Range("A" & Addrow).Value = RefNum
    .Range("B" & Addrow).Value = Input1 & " - " & Input2 & " " & Template
End With

MsgBox "Entry made to table..." & vbCrLf & Input1 & " - " & Input2 & " " & Template

End Sub

What I got in my test...