Excel Vlookup Formula in VBA with Variables

Question

I have Vlookup formula that I recorded in order to enter it to my VBA code with my variables. Somehow, one of the variables is not working good with my formula. sometimes the lastcol from type integer variable is 13 instead of 12 and sometimes its empty and I get

Run time error 1004.

I am not sure that I entered right the variables into the formula in the code. The vlookup takes its data from the Visual worksheet (VisualWS variable).

this is the code where i'm stuck:

Public myExtension As String
Public FullPath As String
Public VisualWB As Workbook
Public VisualWS As Worksheet
Public LR As Long
Public lastcol As Integer
Public MonCol As Integer
Public Table As Range
Public SigilDes As Integer
Public LR_Over As Long

Sub Analyze_1()

Call initialize

With OverWS

    LR_Over = .Cells(Rows.Count, "A").End(xlUp).Row
    .Range("M1").Value = "workdays"
    .Range("M2:M" & LR_Over).FormulaR1C1 = "=VLOOKUP(RC[-12],visual!R2C1:R " & LR & " C " & lastcol & "," & lastcol & ",FALSE)"

End With

this is the initialize sub:

Sub initialize()

Set MainWB = ThisWorkbook
Path = ThisWorkbook.Path
Set ListsWS = MainWB.Worksheets("Lists")
Set VisualWS = MainWB.Worksheets(4)
Set OverWS = MainWB.Worksheets(2)
Set DoubleWS = MainWB.Worksheets(3)
MonthName = UserForm1.ListOfMonths.Value
MainWB.Worksheets(1).Range("F2").Value = MonthName
lastcol = VisualWS.UsedRange.Columns.Count
LR = VisualWS.Cells(Rows.Count, "A").End(xlUp).Row

End Sub

Where do you have the declaration of LR and lastcol? Have you declared them with module-level scope? — YowE3K
I'm not asking where you set the values, I'm asking where you have the declaration statement - i.e. Dim LR As ... or similar. Are they module-level in scope, or just procedure-level? — YowE3K
@YowE3K I made it public before the subs. and I found my mistake, I didn't declared lastcol. you helped me, thank you. — Rafael Osipov
Setting Option Explicit at the top of each code sheet will absolutely avoid such omissions and save you endless time. In the VB Editor go to Tools > Options and check "Require Variable Declaration" on the "Editor" tab. This will automatically ad Option Explicit to every module in all VBA projects created thereafter. — Variatus

Rafael Osipov Rafael Osipov · Accepted Answer · 2018-01-14T08:47:24

Sub Analyze_1()

    Call initialize

    With OverWS

        LR_Over = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("M1").Value = "workdays"
        .Range("M2:M" & LR_Over).FormulaR1C1 = "=VLOOKUP(RC[-12],visual!R2C1:R" & LR & "C" & lastcol & "," & lastcol & ",FALSE)"

    End With
End Sub

The spaces in the formula were the problem

Excel Vlookup Formula in VBA with Variables

1 Answers