Populate wb based on vlookup function with more than one variable

[exc4libur]

My code below works fine..

1. I have a wb [book1.xls] with a table containig 6 columns
2. I open a new wb and add lookup values to column A
3. Then my macro procedure runs vlookup function through all values in Col A and returns only the values.

However.....

I need more than one variable to my Vlookup Function.
I tried to add another variable for worksheet name and i had no success..

Can someone try adding a variable to my function and running it correctly?

Thnks

Sub Working()

Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long

On Error Resume Next 'trap for data in "iv"

   For Each ws In ActiveWorkbook.Worksheets
      With ws
        .Activate
        lastCol = Range("IV1").End(xlToLeft).Column
        lastRow = Range("A65536").End(xlUp).Row
        
        'Determine VlookupColumn Based on Source Sheet Name
Select Case .Name
           Case "Sheet1":
           ReturnColmn = 2
     
           Case "Sheet2":
           ReturnColmn = 3
           
           Case "Sheet3":
           ReturnColmn = 4
           
            Case Else:
            ReturnColmn = 2
            
        End Select
        
        ' Call Vlookup with Variables
        For rw = 1 To lastRow
             LookUpValue = .Cells(rw, 1).Value
            .Cells(rw, lastCol + 1).Value = GetValue(LookUpValue, ReturnColmn)
        Next rw
      End With
Next ws

End Sub

Public Function GetValue(LookUpValue, ReturnColmn)

   result = ExecuteExcel4Macro("VLOOKUP(""" & LookUpValue & """,[Book1.xls]Sheet2!C1:C6,""" & ReturnColmn & """,FALSE)")
   
   If IsError(result) Then
      GetValue = LookUpValue
   Else
      GetValue = result
   End If
End Function

 

[exc4libur]

Just to illustrate the example of what I tried to do...

 Sub NotWorking()

Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long

On Error Resume Next 'trap for data in "iv"

   For Each ws In ActiveWorkbook.Worksheets
      With ws
        .Activate
        lastCol = Range("IV1").End(xlToLeft).Column
        lastRow = Range("A65536").End(xlUp).Row
        
        'Determine VlookupColumn Based on PathFileName and ReturnColmn

Select Case .Name
           Case "Sheet1":
           PathFileName = "[Book1.xls]Table1!C1:C6"
            ReturnColmn = 2
            
            Case Else:
            ReturnColmn = 2
            PathFileName = "[Book1.xls]Table2!C1:C6"
End Select
        
        ' Call Vlookup with Variables
        For rw = 1 To lastRow
             LookUpValue = .Cells(rw, 1).Value
            .Cells(rw, lastCol + 1).Value = GetValue(LookUpValue, PathFileName, ReturnColmn)
        Next rw
      End With
Next ws

End Sub

Public Function GetValue(LookUpValue, PathFileName, ReturnColmn)

'Added new variable: PathFileName
   result = ExecuteExcel4Macro("VLOOKUP(""" & LookUpValue & """,""" & PathFileName & """,""" & ReturnColmn & """,FALSE)")
   
   If IsError(result) Then
      GetValue = LookUpValue
   Else
      GetValue = result
   End If
End Function

 

[exc4libur]

Solution below.....

 Sub Working()

Dim ws As Worksheet
Dim lastCol As Long
Dim lastRow As Long

On Error Resume Next 'trap for data in "iv"

   For Each ws In ActiveWorkbook.Worksheets
      With ws
        .Activate
        lastCol = Range("IV1").End(xlToLeft).Column
        lastRow = Range("A65536").End(xlUp).Row
        
        'Determine Vlookup Based on: ShName, ReturnColmn
Select Case .Name
           Case "Sheet1":
                FullPath = "C:\Users\"UR COMPUTER USERNAME"\Desktop\""
                WbName = "Book1.xls"
                ShName = "Table1"
                SourceRng = "C1:C6"
                ReturnColNum = 2
            
            Case Else:
                FullPath = "C:\Users\"UR COMPUTER USERNAME"\Desktop\"
                WbName = "Book1.xls"
                ShName = "Table2"
                SourceRng = "C1:C6"
                ReturnColNum = 3
            
        End Select
        
        ' Call Vlookup with Variables
        For rw = 1 To lastRow
             SrcValue = .Cells(rw, 1).Value
            .Cells(rw, lastCol + 1).Value = RemoteVlookUp(SrcValue, FullPath, WbName, ShName, SourceRng, ReturnColNum)
            Next rw
      End With
Next ws

End Sub

Private Function RemoteVlookUp(SrcValue, FullPath, WbName, ShName, SourceRng, ReturnColNum) As String
Dim ReturnedValue As String

' NOTE: error will occur even if value cannot be found and everything else is OK

On Error GoTo ErrHandler

   If Not Right(FullPath, 1) = "\" Then FullPath = FullPath & "\"

   ReturnedValue = ExecuteExcel4Macro("VLOOKUP(""" & SrcValue & _
   """,'" & FullPath & _
   "[" & WbName & "]" & _
   ShName & "'!" _
   & SourceRng & "," _
   & ReturnColNum & ",FALSE)")
   
   RemoteVlookUp = ReturnedValue
   Exit Function
ErrHandler:
   RemoteVlookUp = VlookupNA
End Function