0

My code was running properly until today. I'm not sure why I am getting a error with the block variable not being set. The error occurs in my vlookup. 

Sub oversub()
Application.ScreenUpdating = False
    'Latest File Code
    Dim MyPath As String
    Dim MyFile As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date

    MyPath = "C:\Users\TAmon1\Desktop\....."
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    MyFile = Dir(MyPath & "*.csv", vbNormal)
    If Len(MyFile) = 0 Then
        MsgBox "No files were found...", vbExclamation
        Exit Sub
    End If
    Do While Len(MyFile) > 0
        LMD = FileDateTime(MyPath & MyFile)
        If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
        MyFile = Dir
    Loop

Workbooks.Open MyPath & LatestFile
'Variables for Vlookup

Dim wbcsv As Workbook, wbplanning As Workbook
Set wbplanning = Workbooks("Planning_tool.xlsm")
Set wbcsv = Workbooks.Open(MyPath & LatestFile)
Dim wb As Workbook
Set wb = Workbooks.Open(MyPath & LatestFile)

Windows("Planning_tool.xlsm").Activate

#The error occurs here 
wbplanning.Sheets(1).Range("N2").FormulaR1C1 = _
"=VLOOKUP(C[-13],'" & wbcsv.Name & "'!C1:C11,11,FALSE)"
Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N2000")

wbplanning.Sheets(1).Range("o2").FormulaR1C1 = _
"=VLOOKUP(C[-14],'" & wbcsv.Name & "'!C1:C11,5,FALSE)"
Range("o2").Select
Selection.AutoFill Destination:=Range("o2:o2000")
Columns("N:O").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

wb.Close savechanges:=False



End Sub

I get a object variable not set error for my vlookup. The code was working properly earlier so I am not sure what change from yesterday.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
learningguy
  • 43
  • 4
  • Also, why do you use both `wb` and `wbcsv`? – BigBen Aug 05 '19 at 15:48
  • If you do `Debug.Print wbcsv.Name` right before the erroring line, does the Immediate Window return the expected value? – BruceWayne Aug 05 '19 at 16:00
  • I used both just to keep my code more organize. They have the same function – learningguy Aug 05 '19 at 16:09
  • 1
    If you only use the workbook name in your vlookup and do not include the worksheet name, you will get an error. – Darrell H Aug 05 '19 at 16:39
  • 1
    Calling Workbooks.Open() on a file which is already open can have unpredictable outcomes - see https://stackoverflow.com/questions/56780454/can-having-multiple-instances-of-excel-open-cause-vba-issues/56781317#56781317 for example. There's no need to create two variables pointing to the same workbook - it just makes your code confusing to follow. – Tim Williams Aug 05 '19 at 16:52

0 Answers0