Macro Not Copying to Last Row

[spyldbrat]

Can someone tell me what is wrong with below. It is not copying to last row of data in either column.

Range("O2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-12],Collector,3,FALSE),R[-1]C)"
Columns("O:O").EntireColumn.AutoFit
Range("o2" & UsdRws).FillDown


Range("P2").Select
ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Collector,7,FALSE),R[-1]C)"
Range("P2:P" & UsdRws).FillDown
End With

 

[Joe4]

I think you need to look at your section of code that calculates "UsdRws" (which you have not posted here).

 

[Fluff]

@spyldbrat
You do it the same way as here
https://www.mrexcel.com/forum/excel-questions/1025349-macro-copy-last-row-data.html#post4920968

 

[spyldbrat]

My macro doesn't have the UsdRws because each time I added it, I got an error. As Fluff pointed out, I used the macro written by someone else and just modified the ranges (I am very pre-basic on this macro stuff - LOL). The error message highlights "Dim UsdRws As Long" and is a Compile Error: Duplicate declaration in current scope.

Here is what I was using what when I got the error message. I am not exactly sure what it supposed to do so I am unable to edit it...

Dim UsdRws As Long

UsdRws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[spyldbrat]

@spyldbrat
You do it the same way as here
https://www.mrexcel.com/forum/excel-questions/1025349-macro-copy-last-row-data.html#post4920968

Thank you Fluff. I actually tried to use the info in the link you included but I kept getting an error message

 

[Fluff]

Can you please supply your entire macro?
It is almost impossible to help otherwise.
Especially given what you said

The error message highlights "Dim UsdRws As Long" and is a Compile Error: Duplicate declaration in current scope.

Which means you have declared the variable UsdRws twice

 

[Joe4]

Note that if this a follow-up question regarding an existing question (that involves the answer in that previous thread), you should just continue in that thread instead of starting a new one. Otherwise the people who provided the answers might not see the new question and cannot address your concerns about your questions regarding their responses.

My macro doesn't have the UsdRws because each time I added it, I got an error. As Fluff pointed out, I used the macro written by someone else and just modified the ranges (I am very pre-basic on this macro stuff - LOL). The error message highlights "Dim UsdRws As Long" and is a Compile Error: Duplicate declaration in current scope.

Here is what I was using what when I got the error message. I am not exactly sure what it supposed to do so I am unable to edit it...

Dim UsdRws As Long

UsdRws = Cells.Find("*", after:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If you remove the calculation from your code, then you cannot use the variable in the rest of your code (as it is set to nothing!)

[COLOR=#333333]Range("O2").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-12],Collector,3,FALSE),R[-1]C)"[/COLOR]
[COLOR=#333333]Columns("O:O").EntireColumn.AutoFit[/COLOR]
[COLOR=#333333]Range("o2:o" & [/COLOR][COLOR=#ff0000][B]UsdRws[/B][/COLOR][COLOR=#333333]).FillDown[/COLOR]


[COLOR=#333333]Range("P2").Select[/COLOR]
[COLOR=#333333]ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-13],Collector,7,FALSE),R[-1]C)"[/COLOR]
[COLOR=#333333]Range("P2:P" & [/COLOR][B][COLOR=#ff0000]UsdRws[/COLOR][/B][COLOR=#333333]).FillDown[/COLOR]
[COLOR=#333333]End With[/COLOR]

I highly recommend that you put the statement "Option Explicit" at the very top of all your VBA modules.
This requires you to declare all your variables before using them. This helps to identify any typos or if you are using variables that you haven't declared. It will help catch issues like this one.

 

[spyldbrat]

Exactly where would I put "Option Explicit". I tried adding right below the macro name but that didn't work . Do I need to modify the macro above by removing what you have in red? I did think about adding a follow-up to the previous string but since it is working on the what the user provided to me, I thought it might be a new string? My apologies...

 

[Joe4]

put the statement "Option Explicit" at the very top of all your VBA modules.

At the very top, as in before anything else.

Better yet, change the setting so Excel does it automatically for you.
See here for details: http://www.excel-easy.com/vba/examples/option-explicit.html