Macro for SUMPRODUCT formula using dynamic ranges

[Begbie]

Hi guys, hope you can help, I'm sure I'm just making a simple error somewhere.

I have the following code which defines several areas for use in a SUMPRODUCT formula, which works fine.
However, I am getting an issue with the actual sumproduct part. If I type in the formula in excel, then no worries, but VBA doesn't like it.

Any ideas?

    Range("A2").Select
    Range(selection, selection.End(xlDown)).Select
    Set 1_Area = Range(selection, selection.End(xlDown))
    Range("B2").Select
    Range(selection, selection.End(xlDown)).Select
    Set 2_Area = Range(selection, selection.End(xlDown))
    Range("C2").Select
    Range(selection, selection.End(xlDown)).Select
    Set 3_Area = Range(selection, selection.End(xlDown))
    
    Range("G2").Select
    ActiveCell.Formula = Sumproduct((selection.Offset(0, -6) = 1_Area) * (selection.Offset(0, -4) = 2_Area) * (3_Area))

 

[MrKowz]

Is there a reason you need to do this in a macro? You should be able to take care of this with named ranges.

Do you have numeric data or string data in your columns A through C?

 

[Begbie]

A and C have string data, B has numerical.

It needs to be in a macro in order to process the data, but leave no trace of the process - it's all supposed to be part of a bigger macro that enables the push of a single button to process a farily large dataset.

 

[MrKowz]

Understood.

A few things wrong with your code.

• You can't have a variable start with a number like 1_Area, 2_Area, etc.. Instead, use Area1 or Area_1.
• You say that Column C has string data (3_Area in your code), however your SUMPRODUCT is trying to SUM on that column. I assume you need to have it SUM on Column B (your numeric data).
• SUMPRODUCT is not a VBA function, so you can't directly reference the area in the manner you are using it.
• Since SUMPRODUCT requires identically sized references, I would recommend using a single column's number of rows to determine the size of all of the ranges.
• Since you are creating a worksheet function, you must use worksheet function references.
• There is rarely a need to use Select/Selections statements. They slow down the code tremendously and create a lot of confusing when going back to review.

Try the following code:

    Dim LR      As Long, _
        Area1   As Range, _
        Area2   As Range, _
        Area3   As Range
        
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set Area1 = Range("A2:A" & LR)
    Set Area2 = Range("B2:B" & LR)
    Set Area3 = Range("C2:C" & LR)
    Range("G2").Formula = "=SUMPRODUCT(--(" & Area1.Address & "=A2),--(" & Area3.Address & "=C2)," & Area2.Address & ")"

 

[Begbie]

Fantastic, thanks so much.

As you probably noticed with my code, I'm not a natural VBA'er - use it too infrequently to remember how to code efficiently!

Thanks again!

 

[MrKowz]

Not a problem. Thanks for the feedback!