I am getting the runtime error on the vlookup formula line. Eventually I want the file that the vlookup is using to be the histfile variable file name so if someone can help with that, that would be great.
Right now, the information in the vlookup is the current file name that I am testing with and it still doesn't work. It works when I directly paste that formula in the cell but I want the macro to paste that formula to the first 500 or so cells in column B using the histfile variable file name as the lookup table value.
Please help!
Sub Cleanup()
histFile = Application.GetOpenFilename(MultiSelect:=False)
If TypeName(histFile) = "Boolean" Then
MsgBox "No files selected. Activity halted."
Exit Sub
End If
Workbooks.Open Filename:=histFile
userFile = Application.GetOpenFilename(MultiSelect:=False)
If TypeName(userFile) = "Boolean" Then
MsgBox "No files selected. Activity halted."
Exit Sub
End If
Workbooks.Open Filename:=userFile
Range("A1").EntireColumn.Delete
Dim i
For i = 1500 To 1 Step -1
If Cells(i, "A").Value = 0 Then
Cells(i, "A").EntireRow.Delete
End If
Next
Columns("A:A").EntireColumn.AutoFit
Range("B1").Formula = "=IF(ISNA(VLOOKUP(RIGHT(A1,(LEN(A1)-5)),'[HistoryFile 051611.xls]HistoryFile_Report'!$G:$T,14,FALSE)),"",VLOOKUP(RIGHT(A1,(LEN(A1)-5)),'[HistoryFile 051611.xls]HistoryFile_Report'!$G:$T,14,FALSE))"
End Sub
Bookmarks