Sumproduct from different sheet

[bsquad]

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

 

[bsquad]

being unable to include an attachment. I have also posted at this link below where I was able to include an attachment.


Sumproduct from another sheet

 

[FDibbins]

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]

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]

You copy/paste a small sample of your data here, or just explain how your data looks?