Subtraction macro showing an error run time error 1004 application-defined object-defined

Frequent Contributor

How do I resolve the subtraction code below as it is showing an error message:
"error run time error 1004 application-defined object-defined"

 

Sub subract()
    
    Dim ws          As Worksheet
    Dim wsLastrow   As Long
    
    Set ws = ThisWorkbook.Sheets("Account Level")
    
    wsLastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    ws.Range("G2:G" & wsLastrow) = Evaluate("E2:E" & wsLastrow & "-F2:F" & wsLastrow)
    ws.Range("G2:G" & lastRowK).NumberFormat = "#,##0.00"
    
End Sub

 

thanks and appreciate the help

5 Replies

@hrh_dash 

In the line

    ws.Range("G2:G" & lastRowK).NumberFormat = "#,##0.00"

you use lastRowK instead of wsLastrow.

Since lastRowK has not been assigned a value, it is 0, which is not a valid row number.

If you had required all variables to be declared explicitly, you would have been notified of this.

See The importance of 'Option Explicit' 

Hi @Hans Vogelaar , after correcting it to 
ws.Range("G2:G" & wsLastrow).NumberFormat = "#,##0.00", my results are populated as #value.

 

Tried using this code below for a separate workbook, it does work. 

ws.Range("G2:G" & wsLastrow) = Evaluate("E2:E" & wsLastrow & "-F2:F" & wsLastrow)

 

not sure why it is populating #value now for this workbook, is it due to blank cells in row F?

 

 

 

@hrh_dash 

That's impossible to know without seeing the workbook

Apologies for the false alarm, I managed to rectify the issue by placing the macro into a module. Managed to get the code working nicely.