Excel-Vba : Code for Applying Formula until Last Row not working

Question

I'm new to VBA so sorry if this seems to be a simple question.

I'm trying to create a macro which will formate and include a couple of formulas in a sheet but when I try to include the formula until the last row I get a error "Run Time Error 1004 - Application-Defined or Object Defined Error" at the following code:

ActiveSheet.Range("U2:U" & LastRow).Formula = "=L2/86400"

If I change the "Last Row" for a number the Macro works normally. Below is the whole code.

Sheets("DLASpotPlacement").Select
Dim LastRow As Double
LastRow = Sheets("DLASpotPlacement").Cells(Rows.Count, 1).Rows
Range("A1").Select
ActiveSheet.Range("U:U, V:V, W:W").NumberFormat = "[h]:mm:ss;@"
ActiveSheet.Range("U2:U" & LastRow).Formula = "=L2/86400"
ActiveSheet.Range("V2:V" & LastRow).Formula = "=VALUE(H2)"
ActiveSheet.Range("W2:W" & LastRow).FormulaLocal = "=IF(AND(H2>0,0416666666666667;H2<=0,249988425925926);""01 - 06"";IF(AND(H2>=0,25;H2<0,4166551);""06 - 10"";IF(AND(H2>=0,4166667;H2<0,4999884);""10 - 12"";IF(AND(H2>=0,5;H2<0,7499884);""12 - 18"";""18 - 01""))))"

Thanks for all the help

LastRow = Sheets("DLASpotPlacement").Cells(Rows.Count, 1).End(xlup).Row — Scott Craner
– Scott Craner, Commented Feb 11, 2019 at 23:11
@ScottCraner Thank you! I see now it was a dumb error. Now it's working perfectly! — Felipe Ferreira de Oliveira
– Felipe Ferreira de Oliveira, Commented Feb 13, 2019 at 16:35

VBasic2008 · Accepted Answer · 2019-02-12 00:40:27Z

Copy Excel Formulas

The error occurs because of two reasons:

You forgot End(xlUp) in the LastRow Calculation, e.g.:

LastRow = Sheets("DLASpotPlacement").Cells(Rows.Count, 1).End(xlUp).Row

and it has to be declared as a whole number e.g.:

Dim LastRow as Long

The Code

Option Explicit

Sub CopyFormulas()

    Const cCol As Variant = "A"   ' Last Row Column Letter/Number
    Const cFirstR As Long = 2     ' First Row Number

    Dim LastRow As Long           ' Last Row Number

    With ThisWorkbook.Worksheets("DLASpotPlacement")
        LastRow = .Cells(.Rows.Count, cCol).End(xlUp).Row
        '.Cells(1, cCol).Select ' uncomment if necessary
        ' You don't need to format the entire columns.
        .Range("U" & cFirstR & ":W" & LastRow).NumberFormat = "[h]:mm:ss;@"
        .Range("U" & cFirstR & ":U" & LastRow).Formula = "=L2/86400"
        .Range("V" & cFirstR & ":V" & LastRow).Formula = "=VALUE(H2)"
        .Range("W" & cFirstR & ":W" & LastRow).FormulaLocal = _
                "=IF(AND(H2>0,0416666666666667;H2<=0,249988425925926);""" _
                & "01 - 06"";IF(AND(H2>=0,25;H2<0,4166551);""06 - 10"";IF(" _
                & "AND(H2>=0,4166667;H2<0,4999884);""10 - 12"";IF(AND(H2>=0" _
                & ",5;H2<0,7499884);""12 - 18"";""18 - 01""))))"
    End With

End Sub

Remarks

Using FormulaLocal is a nice 'trick' to remember.

user8221156 · Accepted Answer · 2019-02-12 00:26:06Z

1

@Mike; Your problem is in this line:

LastRow = Sheets("DLASpotPlacement").Cells(Rows.Count, 1).Rows

You made the LastRow an array, not a number. Also, is not a Double but an Iteger (mathematically). However, the Integer datatype is too small and you will get an "Overflow" error if you declare it "As Integer". Here are the two changes you need to make it all work:

Dim LastRow As Long
LastRow = Sheets("DLASpotPlacement").Rows.Count
...

answered Feb 12, 2019 at 0:26

user8221156

Comments

Mike · Accepted Answer · 2019-02-11 23:43:11Z

0

For LastRow, use the Worksheet.UsedRange property.

You could also use the Range.Resize property to select the range, and replace the "Select" with "With".

Dim LastRow As Double
With Sheets("DLASpotPlacement")
    LastRow = .UsedRange.Rows.count
    .Range("U:W").NumberFormat = "[h]:mm:ss;@"
    .Range("U1").Resize(LastRow - 1).Formula = "=L2/86400"
    .Range("V1").Resize(LastRow - 1).Formula = "=VALUE(H2)"
    .Range("W1").Resize(LastRow - 1).FormulaLocal = "..."
End With

answered Feb 11, 2019 at 23:43

Mike

92 bronze badges

Collectives™ on Stack Overflow

Excel-Vba : Code for Applying Formula until Last Row not working

3 Answers 3

Copy Excel Formulas

The Code

Remarks

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Copy Excel Formulas

The Code

Remarks

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related