Issue with Excel Macro selecting correct cell

Question

I am creating an excel macro to format text to a usable excel file. After doing some cleanup and formatting of the date I get the date in rows and columns and remove unnecessary columns. Once that is done I am doing a VLOOKUP against another sheet in the file to return a string. The code that I have has extra comments because I built sections separately then put them together in a single macro.

The issue that I am having is that the VLOOKUP runs and the macro completes but it starts in column G where the last active cell was from the Cleanup_File Macro.

I have tried to add 'Range("A1").Select and/or 'Range("A1").Activate but get 'Range("A1").Activate and I get:

Run-time error '1004': Application-defined or object-defined error

I do have the Range commands commented out to prove the file will work without them

Here is the code

Cleanup_File Macro

'

'

Columns("A:B").Select 

Selection.Delete Shift:=xlToLeft 

Columns("G:L").Select 

Selection.Delete Shift:=xlToLeft 

' End Sub

' Sub Vlookup()

'

' Vlookup Macro

'Range("A1").Select

'Range("A1").Activate

Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FormulaR1C1 = _

"=VLOOKUP(RC[-1],Trucks!A:B,2,FALSE)"

Answer 1

For some reason when I run this

Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Trucks!A:B,2,FALSE)"

It changes the vlookup to this             =VLOOKUP(A1,Trucks!A:(B),2,FALSE)

Note the 2^nd^ column in the vlookup is in parentheses and if I remove them in the cell the vlookup works correctly

Also I wanted to add an IFERROR and return a blank instead of #N/A, if that is not possible, I will just filter that out on a subsequent step

=IFERROR(VLOOKUP(A1,Trucks!A:B,2,FALSE),"")

Answer 2

Hi Don

Please, try the following codes

Sub Cleanup_File()

Columns("A:B").Delete

Columns("G:L").Delete

End Sub

Sub Vlookup()

Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)).Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],Trucks!A:B,2,FALSE)"

End Sub

I hope this helps you

Regards

Jeovany