Loop help

[wisemank]

Last question for today...I hope.


I want to find the last row with data and call that value from the loop in my vlookup formula (bold red), as opposed to current hard coded values. These amount of rows will be different each time I run.


Here is my code:

Sheets("VSC to FGA").Select
Range("D2").Select
Range("D2:D457" & LR).Formula = "=IF(ISERROR(VLOOKUP($C2,'Matricola Map'!B:G,4,FALSE)),""Not Found"",(VLOOKUP($C2,'Matricola Map'!B:G,4,FALSE)))"
Range("E2:E457" & LR).Formula = "=IF(ISERROR(VLOOKUP($D2,'Matricola Map'!E:F,2,FALSE)),""Not Found"",(VLOOKUP($D2,'Matricola Map'!E:F,2,FALSE)))"
Range("D2:E457").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False

Range("A1").Select

 

[BiocideJ]

Range(Range("D2"), Range("D2").End(xlDown)).Formula = ...
Range(Range("E2"), Range("E2").End(xlDown)).Formula = ...
Range(Range("D2"), Range("F2").End(xlDown)).Select

this assumes there are no breaks (blank cells) between row 2 and the last cell with values in each column.

If there are blanks then to find the last cell use the following adaptation:

Range(Range("D2"), Range("D" & Rows.Count).End(xlUp)).Formula = ...
Range(Range("E2"), Range("E" & Rows.Count).End(xlUp)).Formula = ...
Range(Range("D2"), Range("E" & Rows.Count).End(xlUp)).Select

 

[texasalynn]

Create a variable for the last row
LR = Range("A" & Rows.Count).End(xlUp).Row

then you can use that variable in your work

 Range("D2:D" & LR).Formula = "=IF(ISERROR(VLOOKUP($C2,'Matricola Map'!B:G,4,FALSE)),""Not Found"",(VLOOKUP($C2,'Matricola Map'!B:G,4,FALSE)))"

 

[wisemank]

Option 2 with Blanks worked and very fast.
Option 1 worked, but took a very long time.

You guys are the best...Thanks

 

[BiocideJ]

Option 2 with Blanks worked and very fast.
Option 1 worked, but took a very long time.

You guys are the best...Thanks

Strange that either took a long time since they do basically the same thing. I tested all on my computer and even when the range was 1M rows it finished nearly instantly for me. At any rate, glad it is up and running now.