VBA SumProduct using ranges set earlier in the macro

[storm170]

Hello all,

I am attempting to write a macro to automate a process and have run into some problems with the SumProduct formula I use to calculate the value at the end of the process. I have read a number of sites today about how VBA has problems with the SumProduct formula, but there were workarounds. Unfortunately, I am new to VBA so converting the solutions to my specific example were a bit beyond me.

If you could review my code below and let me know of any suggestions I would greatly appreciate it. Below is a summary of what I am trying to do with the macro.

Summary - The macro is working up to the point of using the formula to work on the results the macro has produced up to that point. I am attempting to compare a value that was copied to compare to the initial range that I set to "rng", and when those two values match sum the values in those rows in the "Calcrng" range that I set earlier in the macro. The formula itself has been tested and works correctly, its converting it to VBA and having it reference the ranges rather than static cells that I can't figure out. I know the formula I have listed below won't work, I've tried to use Application.Evaluate and tinker with additional " " around certain parts of the formula to no avail.

<code>Sub AnnivLIDTotal()

Dim rng As Range
Dim Start As Range
Dim Duplicate As Range
Dim Calcrng As Range
    
Set rng = Range(ActiveCell, ActiveCell.End(xlDown))
Set Start = ActiveCell
Set Calcrng = Range(rng.Offset(0, 14), rng.Offset(0, 15))

rng.Offset(0, 5).Select
    rng.Offset(0, 5).Copy
Start.Offset(0, 22).Select
ActiveSheet.Paste
Application.CutCopyMode = False
    
Set Duplicate = Selection
Duplicate.RemoveDuplicates Columns:=1, Header:= _
    xlNo

Start.Offset(0, 23).Select
ActiveCell.Formula = "=SUMPRODUCT(rng=Start.Offset(0,22)*Calcrng)"

End Sub</code>

Please let me know if there is any additional information I can provide to help produce a solution. Thanks!

 

[storm170]

To the top with you, scurvy dog!

 

[storm170]

Hello all,

Bumping this one last time before I'll be assuming that I need to change the process itself or look elsewhere for assistance. Please let me know if there is any additional information I can provide to help us come up with a solution for this. Thanks!