CODEWITHSUNDEEP
excelvba
Matt Ridge
Matt Ridge

Reputation: 3651

Lastrow going up instead of down in Excel VBA

Here is my code it's a little off, although it does work, just not the way I want it to. 

Option Explicit

Sub ONJL()
    Dim lastrow As Long
    Dim wsPAR As Worksheet 'PAERTO
    Dim wsRD As Worksheet 'Raw Data
    Dim wsTEM As Worksheet 'Archive

    Set wsPAR = Sheets("PAERTO")
    Set wsRD = Sheets("Raw Data")
    Set wsTEM = Sheets("Template")

    With wsPAR
            Application.ScreenUpdating = False
            wsPAR.Range("B23:I300").Clear
            lastrow = wsRD.Range("B3").Row + 23
            wsTEM.Range("A23:H23").Copy wsPAR.Range("B23:I" & lastrow)
            .Range("F4").Formula = "=SUMPRODUCT(--(I23:I" & lastrow & ">='Raw Data'!K2),--(I23:I" & lastrow & "<='Raw Data'!K3))"
            .Range("F5").Formula = "=SUMPRODUCT(--(I23:I" & lastrow & ">='Raw Data'!K3),--(I23:I" & lastrow & "<='Raw Data'!K4))"
            .Range("F6").Formula = "=SUMPRODUCT(--(I23:I" & lastrow & ">='Raw Data'!K4),--(I23:I" & lastrow & "<='Raw Data'!K5))"

            lastrow = wsRD.Range("E3").Row + 23
            wsTEM.Range("I23:U23").Copy wsPAR.Range("M23:Y" & lastrow)


            Application.ScreenUpdating = True

    End With

End Sub

Now B3 is 7, and E3 is 25.

I figured that if I typed in as I did the +23 because the data starts on 23, and should end where the lastrow equals so it should be technically I30 and Y48, but it's not.

Can someone give me an idea why?

Upvotes: 0

Views: 201

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

Replace the .Row in the two lines below with .Value

lastrow = wsRD.Range("B3").Row + 23
lastrow = wsRD.Range("E3").Row + 23

What you are essentially asking VBA to do is get the row number of the range, 3, in both cases and add it to 23. If you want to get the value inside each range and add 23 to it, you need to use the .Value property.

Upvotes: 3

Related Questions