Aug 02 2022 05:23 AM
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
Aug 02 2022 05:30 AM
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.
Aug 02 2022 07:19 AM - edited Aug 02 2022 07:57 AM
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?
Aug 02 2022 07:55 AM
That's impossible to know without seeing the workbook
Aug 02 2022 08:31 AM
Aug 02 2022 10:13 AM
Thanks for the feedback.