Snasui Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
    With Worksheets("Input")
.Range("InputName").Formula = "=IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH00!$A$2:$G$10,6,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH01!$A$2:$G$10,6,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH10!$A$2:$G$10,6,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SNH01!$A$2:$G$10,6,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SNH10!$A$2:$G$10,6,0),"""")))))"
.Range("InputDOB").Formula = "=IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH00!$A$2:$G$10,7,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH01!$A$2:$G$10,7,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SVH10!$A$2:$G$10,7,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SNH01!$A$2:$G$10,7,0),IFERROR(VLOOKUP(InputHN,[ListHNSVNH1.xlsx]SNH10!$A$2:$G$10,7,0),"""")))))"
          
    End With
End Sub

Code: Select all

Dim sb As Workbook
Dim sh As Worksheet
Dim rngA As Range
Dim r As Range
Dim strSb As String
Dim hn As String
Dim f As Boolean
strSb = "C:\ListHNSVNH1.xlsx"
Set sb = Workbooks.Open(Filename:=strSb, UpdateLinks:=False, ReadOnly:=True)
f = False
With ThisWorkbook.Worksheets("Input")
    hn = .Range("b3").Value
    For Each sh In sb.Worksheets
        Set rngA = sh.Range("a2", sh.Range("a" & sh.Rows.Count).End(xlUp))
        For Each r In rngA
            If r.Value = hn Then
                .Range("b4").Value = r.Offset(0, 5).Value
                .Range("b5").Value = r.Offset(0, 6).Value
                f = True
                Exit For
            End If
        Next r
        If f Then Exit For
    Next sh
End With
sb.Close False

Code: Select all

...other code....

strSb = "\\svh-sv-vcifs01\Test\Estimated Form\ListHNSVNH1.xlsx"

...other code....