Dynamically referencing previous sheet with VBA vlookup function

[JRedRocker]

I am brand new to VBA. Just started recording macros and trying to modify them to work for my purposes.

I am trying to get the following code to reference the previous sheet dynamically rather than the sheet I selected when recording the macro (Oct 31) then autofilling to the end of the data.

LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("U2:U" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-16],'Oct 31'!C[-16]:C[1],17,FALSE)"

I found another thread that said to use activesheet.previous.select but I can't seem to replace 'Oct 31' with that code to make it work.

Suggestions please?

 

[AlphaFrog]

Hi and welcome to the forum.

Try something like this...

Range("U2:U" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-16],'" & ActiveSheet.Previous.Name & "'!C[-16]:C[1],17,FALSE)"

 

[JRedRocker]

Hi and welcome to the forum.

Try something like this...

Range("U2:U" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-16],'" & ActiveSheet.Previous.Name & "'!C[-16]:C[1],17,FALSE)"

That worked perfectly! Thank you for the help

 

[nlahon]

That worked perfectly! Thank you for the help

Hi I am also facing this kind of issue, wherein i applied the above formula but its giving me Run time Error "1004" - "Method 'Range' of Object_Global failed"

please help.

 

[AlphaFrog]

Hi I am also facing this kind of issue, wherein i applied the above formula but its giving me Run time Error "1004" - "Method 'Range' of Object_Global failed"

please help.

Did you calculate the last row?

[B]LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row[/B]
Range("U2:U" & [B]LastRow[/B]).FormulaR1C1 = "=VLOOKUP(RC[-16],'" & ActiveSheet.Previous.Name & "'!C[-16]:C[1],17,FALSE)"

 

[nlahon]

Did you calculate the last row?

[B]LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row[/B]
Range("U2:U" & [B]LastRow[/B]).FormulaR1C1 = "=VLOOKUP(RC[-16],'" & ActiveSheet.Previous.Name & "'!C[-16]:C[1],17,FALSE)"

Oh yes, i forgot to do that. now its working.

Thanks a lot " AlphaFrog" and everyone who participated in this forum

 

[sunithahal]

it doesn't work in the below formula
ActiveCell.FormulaR1C1 = _
"=IF(ISNA(IF(VLOOKUP(R[2]C[3],'" & ActiveSheet.Previous.Name & "' !R2C10:R1000C12,3,FALSE)=R[2]C[5],"""",VLOOKUP(R[2]C[3],'" & ActiveSheet.Previous.Name & "'!R2C10:R1000C12,3,FALSE))),""New"",IF(VLOOKUP(R[2]C[3],'" & ActiveSheet.Previous.Name & "'!R2C10:R1000C12,3,FALSE)=R[2]C[5],"""",VLOOKUP(R[2]C[3],'" & ActiveSheet.Previous.Name & "'!R2C10:R1000C12,3,FALSE)))"


the actual formula is

=IF(ISNA(IF(VLOOKUP(J3,'Oct-17'!$J$2:$L$1000,3,FALSE)=L3,"",VLOOKUP(J3,'Oct-17'!$J$2:$L$1000,3,FALSE))),"New",IF(VLOOKUP(J3,'Oct-17'!$J$2:$L$1000,3,FALSE)=L3,"",VLOOKUP(J3,'Oct-17'!$J$2:$L$1000,3,FALSE)))

where the 'oct-17 ' refers to the previous sheet

Please help me in making this work

 

[Excel_Blonde]

Hi there,

I've been trying to VBA Vlookup back to the previous worksheet and found this thread very useful.
After some trial and error I managed to change the formula to suit my information and eventually ended up with data in the requested column, now I think I have the formula correct but it now returns #Ref ??? So there is obviously something wrong but I cant see what it is.

The data I am trying to reference is column B, and return the data from column C, I have headings on the reference sheet in B2 and the data starts from B3, could this be causing the issue?

LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("C2:C" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],'" & ActiveSheet.Previous.Name & "'!C[-1]:C[-1],2,FALSE)"

Any help would be greatly appreciated.

 

[Excel_Blonde]

Hi there,

I've been trying to VBA Vlookup back to the previous worksheet and found this thread very useful.
After some trial and error I managed to change the formula to suit my information and eventually ended up with data in the requested column, now I think I have the formula correct but it now returns #Ref ??? So there is obviously something wrong but I cant see what it is.

The data I am trying to reference is column B, and return the data from column C, I have headings on the reference sheet in B2 and the data starts from B3, could this be causing the issue?

LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Range("C2:C" & LastRow).FormulaR1C1 = "=VLOOKUP(RC[-1],'" & ActiveSheet.Previous.Name & "'!C[-1]:C[-1],2,FALSE)"

Any help would be greatly appreciated.

After correcting C[-1]:C[-1] to C[-1]:C[-5] I now understand where I went wrong.