CODEWITHSUNDEEP
vbaexcel
K.EL
K.EL

Reputation: 65

Using Dynamic Range in Excel without using Named Ranges

I am trying to write a macro which will run for the entire length of a worksheet and do the calculations accordingly.

This is the code I had for the static Range:

With .Range("P2:P" & LastRow)
    .Formula = "=SUMIF('Sheet2'!A$2:A$530,'Sheet1'!N2,'Sheet2'!B$2:B$530)"
    .Value = .Value
    Columns("P:P").Select
    Selection.NumberFormat = "0.00"
End With

I have a basic idea of what I'm trying to do here but because I am new to VBA I can't really figure out how to do it.

This is the current code I'm Trying to Write:

 With .Range("P2:P" & LastRow)
        .Formula = "=SUMIF(Worksheets('Sheet2').Range("A2:A"& LR),'Sheet1'!N2,'Sheet2'!B$2:B$530)"
        .Value = .Value
        Columns("P:P").Select
        Selection.NumberFormat = "0.00"
    End With

However, for this it keeps on giving me an end of statement error, which I want to fix.

Also, LastRow and LR contain the last cell number of both the sheets respectively.

Upvotes: 1

Views: 100

Answers (1)

SJR
SJR

Reputation: 23081

Try this. Your syntax was a little off in the first bit of your SUMIF, and you were hardcoding the final row number. Note too there is no need to select.

With .Range("P2:P" & LastRow)
    .Formula = "=SUMIF(Sheet2!A$2:A$" & LR & ",'Sheet1'!N2,'Sheet2'!B$2:B" & LR & ")"
    .Value = .Value
    .NumberFormat = "0.00"
End With

Upvotes: 2

Related Questions