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
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