Search code examples
excelvbavlookupworksheet-function

vlookup to specific worksheet

I populate a sheet's column A data from a different sheet.

The vlookup referring to that specific data in its specific sheet isn't working and Excel is popping a window to select the sheet.

Part of my code is as follows:

Dim i As Integer
Dim fdof As Date
fdof = Date - Day(Date) + 1
j = 2
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*2018" Or ws.Name Like "*2019" Then

        For i = 2 To ws.Range("A1").SpecialCells(xlLastCell).Row

           If Evaluate("OR(ISNUMBER(MATCH({""*-*""},{""" & ws.Cells(i, 1).Value & """},0)))") And ws.Cells(i, 5).Value = "Vacant" And ws.Cells(i, 3).Value >= fdof Then

                Sheets("Rapport de Disponibilité").Cells(j, 1) = ws.Cells(i, 1)
                Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,3,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 3).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,4,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 4).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,8,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 5).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,15,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 6).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,16,FALSE),"""")"
'                Sheets("Rapport de Disponibilité").Cells(j, 7).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,20,FALSE),"""")"

                j = j + 1
            End If

        Next i
    End If
Next ws

I believe the error is in this line at ws!

Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ",ws!$A:$T,3,FALSE),"""")"
Solution

  • Try,

    Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", '" & ws.name & "'!$A:$T, 3, FALSE), text(,))"
'alternate
Sheets("Rapport de Disponibilité").Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 3, FALSE), text(,))"

    The logic of your EVALUATE doesn't really make sense. FIND would be better than MATCH and I don't see a second statement for the OR.

    ...
If cbool(instr(1, ws.Cells(i, 1).Value, "-")) And ws.Cells(i, 5).Value = "Vacant" And ws.Cells(i, 3).Value >= fdof Then
...

    Your formula assignment would be more efficient and more readable inside a With ... End With block.

    ...
with workSheets("Rapport de Disponibilité")
    .Cells(j, 1) = ws.Cells(i, 1)
    .Cells(j, 2).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 3, FALSE), text(,))"
    .Cells(j, 3).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 4, FALSE), text(,))"
    .Cells(j, 4).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 8, FALSE), text(,))"
    .Cells(j, 5).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 15, FALSE), text(,))"
    .Cells(j, 6).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 16, FALSE), text(,))"
    .Cells(j, 7).Formula = "=IFERROR(VLookup($A" & j & ", " & ws.range("A:T").address(0, 0, external:=true) & ", 20, FALSE), text(,))"
end with
...