subtract two columns

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

subtract two columns

Post by adam »

I'm having a feeling that there's an error in line

Code: Select all

  LastRow = Cells(Rows.Count, "Y").End(xlUp).Row
of my code which is not making the code work accordingly.

What I'm trying to do in the code is to subtract two columns and to place the result in column AA. My data row starts from row 5. I have headings in row 3 and 4.

Code: Select all

Sub Subtract()
    Dim LastRow      As Long
    Dim ws           As Worksheet
    
    Set ws = Worksheets("Report")
    
    LastRow = Cells(Rows.Count, "Y").End(xlUp).Row
    ws.Range("AA5:AA" & LastRow) = Evaluate("Y5:Y" & LastRow & "-Z5:Z" & LastRow)
End Sub
The code does subtract the columns. But it gives 0 values at times.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: subtract two columns

Post by HansV »

If ws is not the active sheet, the line

Code: Select all

    LastRow = Cells(Rows.Count, "Y").End(xlUp).Row
may return an incorrect value, since it refers to the active sheet. Try this version:

Code: Select all

Sub Subtract()
    Dim ws           As Worksheet
    Dim LastRow      As Long
    
    Set ws = Worksheets("Report")
    
    LastRow = ws.Cells(ws.Rows.Count, "Y").End(xlUp).Row
    With ws.Range("AA5:AA" & LastRow)
        .Formula = "=Y5-Z5"
        .Value = .Value
    End With
End Sub
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
Joined: 23 Feb 2010, 12:07

Re: subtract two columns

Post by adam »

Thanks for the help Hans. It worked well.
Best Regards,
Adam