Search code examples
excelvbavariablesvlookupvariant

Vba to Vlookup with variable SheetName

The following code portion should perform Vlookup with variable sheetname. However, the vlookup returns only 0's not the desired value from the lookup range, considering that the lookup range has the same structure across these multiple sheets with variable name, so any advise.

Dim SheetName As Variant: SheetName = DestinationWs.Name

Dim OldRemainingHoursLastRowFirst As Variant
OldRemainingHoursLastRowFirst = DestinationWs.Range("W3").End(xlDown).Row

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],""'" & StringVal & "'!R4C12:R" & OldRemainingHoursLastRowFirst & "C14"",2,0),0)"

After performing the following code, this is the snapshot from the formula bar

Solution

  • Too many quotes?

    Dim SheetName As Variant
SheetName= DestinationWs.Name

DestinationWs.Range("L4").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-2],INDIRECT('" & _
             SheetName & "'!R[" & OldRemainingLastRowFirst & "]C[12]:R[" & _
             OldRemainingLastRowFirst & "]C[12]), 2, 0)"

    After post updates:

        Dim srIndex, sMonth, destinationWs As Worksheet, lRow As Long
    Dim wsMonth As Worksheet, wsPrevMonth As Worksheet
    Dim selMonth, prevMonth, lRow As Longo
    
    '...
    
    selMonth = MonthResultRg.Cells(Application.match(CLng(InputValue), IndexRange, 0))
    prevMonth = MonthResultRg.Cells(Application.match(CLng(InputValue) - 1, IndexRange, 0))
    
    'reference selected and previous months' sheets
    Set wsMonth = FindSheet(ThisWorkbook, selMonth)
    Set wsPrevMonth = FindSheet(ThisWorkbook, prevMonth)
    lRow = wsPrevMonth.Range("W3").End(xlDown).Row
    
    wsMonth.Range("L4").Formula = _
      "=IFERROR(VLOOKUP(J4,'" & wsPrevMonth.Name & "'!L4:N" & lRow & ",2,0),0)"

    FindSheet Function:

    'return the first sheet in `wb` whose name contains `txt`
Function FindSheet(wb As Workbook, txt) As Worksheet
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        If InStr(1, ws.Name, txt, vbTextCompare) > 0 Then
            Set FindSheet = ws
            Exit Function
        End If
    Next ws
End Function