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.
Please let me know if there is any additional information I can provide to help produce a solution. Thanks!
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:
<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!