Using Dynamic Range in Excel without using Named Ranges

Question

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.

SJR · Accepted Answer · 2018-04-24 15:47:53Z

2

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

edited Apr 24, 2018 at 15:47

answered Apr 24, 2018 at 15:29

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dbmitch Over a year ago

Good job on removing the SELECT - you can probably also remove .Value = .Value - that doesn't seem to be doing much

SJR Over a year ago

@dbmitch - that replaces the actual formulae with their results (i.e. equivalent to copy/pastespecial values only).

Collectives™ on Stack Overflow

Using Dynamic Range in Excel without using Named Ranges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related