Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Aug 02, 2022

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

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

excel
Formulas and Functions
Macros and VBA

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 02, 2022

    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' 

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      Aug 02, 2022

      Hi HansVogelaar , 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?

       

       

       

Resources