CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

Excel Vlookup Formula in VBA with Variables

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

Upvotes: 0

Views: 1788

Answers (1)

Rafael Osipov
Rafael Osipov

Reputation: 740

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

Upvotes: 2

Related Questions