If then statement not working

[mbkinzer]

Hello,

My current macro works but I really need it to be an IF THEN statement. It currently looks at Sheet "RTW Report" range U2 to last row minus 2 and copies any cells with values into another sheet called "Charges" into C18. It then does the same thing for Column V on the "RTW Report" sheet and pastes those values under the last row in column C on the "Charges" sheet.

However, sometimes there are no values in Columns U or V and if so it is currently bombing out. I think an IF then statement will work but I am having trouble with it. Thanks!

Current macro:

Dim lr As Long

Sheets("RTW Report").Activate
lr = Cells(Rows.Count, "U").End(xlUp).Row
Sheets("RTW Report").Range("U2:U" & lr - 2).SpecialCells(xlCellTypeConstants, xlNumbers).Copy Sheets("Charges").Range("C18")

Dim lr2 As Long

Sheets("RTW Report").Activate
lr2 = Cells(Rows.Count, "V").End(xlUp).Row
Sheets("Charges").Activate
Sheets("RTW Report").Range("V2:V" & lr2 - 2).SpecialCells(xlCellTypeConstants, xlNumbers).Copy Sheets("Charges").Range(Cells((CHARGE1 + 18), 3), Cells((CHARGE1 + 18), 3))

 

[Joe4]

However, sometimes there are no values in Columns U or V and if so it is currently bombing out. I think an IF then statement will work but I am having trouble with it. Thanks!

What is the value in lr in those cases?

Maybe you need something like:

If lr >= 4 Then
    Sheets("RTW Report").Range("U2:U" & lr - 2).SpecialCells(xlCellTypeConstants, xlNumbers).Copy Sheets("Charges").Range("C18")
End If

 

[mbkinzer]

What is the value in lr in those cases?

Maybe you need something like:

If lr >= 4 Then
    Sheets("RTW Report").Range("U2:U" & lr - 2).SpecialCells(xlCellTypeConstants, xlNumbers).Copy Sheets("Charges").Range("C18")
End If[/CO
[/QUOTE]

 

[mbkinzer]

lr value is numerical.

This solution worked, thank you!!

 

[Joe4]

You are welcome.

I knew it was a number, I was just wondering if it actually correctly identified when there is no data.

 

[mbkinzer]

You are welcome.

I knew it was a number, I was just wondering if it actually correctly identified when there is no data.

Sorry, actually I ran it with data in those cells. I just reran and it is still bombing out when there are no values.

 

[Joe4]

Sorry, actually I ran it with data in those cells. I just reran and it is still bombing out when there are no values.

OK, tell us exactly what "lr" is returning in that situation.

You can add a line of code like this, if you aren't sure how to get it from your code:

lr = Cells(Rows.Count, "U").End(xlUp).Row
MsgBox lr

Also, is it erroring out on that line, or a line below lt (hit "Debug" and see which line of code it is highlighting)?
You may need to apply the same logic for "lr2".