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