Sumproduct from different sheet

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
I am not exactly sure how to attach a sample file, but I do have one if someone lets me know how to do so


I have a set of ID numbers on the 'Ouput' tab, and on the 'Data' tab; the same IDs exist but some are broken out. I am trying to sumproduct the IDs on the Data tab.

the error I am getting is Runtime error '91' Object variable or with block variable not set


Thanks so much

Sub Test_xFill_L_v2()


Dim LR As Long, _
Area1 As Range, _
Area2 As Range, _
Area3 As Range
Dim r As Range, c As Range, a As Range, d As Range
Dim LastRow As Integer

LastRow = Worksheets("Output").Cells(Rows.Count, 1).End(xlUp).Row

Set d = Worksheets("Output").Range("L3" & LastRow)



For Each c In d.Cells

'ER = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
LR = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
Set Area1 = Range("A3:A" & LR)
Set Area2 = Range("X3:X" & LR)
'Set Area3 = Range("C2:C" & LR)

Sheets("Output").Range("L3").Formula = "=SUMPRODUCT(--(" & Area1.Address & "=$A3)," & Area2.Address & ")"

Next c

For Each a In r.Areas
With Sheets("Output").Range(a.Address)
.Value = .Value
End With
Next a











End Sub
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
1. Forum does not allow uploading files :(
2. Is there a reason you need this in VBA? Possibly a regular formula will do what you want?
3. Can you show some sample data here, I am not permitted to access file hosting sites
 

bsquad

Board Regular
Joined
Mar 15, 2016
Messages
194
FDibbins,

Thank you for the reply. I unfortunately have to do it through VBA because there could potential be up to 100k rows, and keeping formulas in there instead of .value=.value crashes Excel.

If you know of a way I can post sample data with out it looking all unformatted in this thread please let me know, Id be more than happy to post
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
You copy/paste a small sample of your data here, or just explain how your data looks?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,326
Messages
5,641,539
Members
417,217
Latest member
nextlove

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top