CODEWITHSUNDEEP
excelvba
Felipe Ferreira de Oliveira
Felipe Ferreira de Oliveira

Reputation: 23

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

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

Upvotes: 2

Views: 972

Answers (3)

VBasic2008
VBasic2008

Reputation: 54777

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.

Upvotes: 1

user8221156
user8221156

Reputation:

@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
...

Upvotes: 1

Mike
Mike

Reputation: 9

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

Upvotes: 0

Related Questions