Error 1004 when referencing specific cells within a defined range

Question

Edited to add solution: I was trying to do Sumproduct and I'm a big dummy haha

I'm trying to do a pretty simple calculation in VBA where I need to loop through two ranges and multiply specific cells then add the value to a placeholder value.

The code is:

Dim Input_Cas As Range
    Set Input_Cas = Worksheets("Calculations").Range("A2:A51")
Dim Input_Conc As Range
    Set Input_Conc = Worksheets("Calculations").Range("C2:C51")
    i = 1
Do Until IsEmpty(Range("Input_Cas")(i)) = True
    Tkm = Tkm + Range("Input_Tki")(i) * (Range("Input_Conc")(i) / 100)
    i = i + 1
Loop
Mix_Tkm = Tkm

The Ranges defined as Input_Conc, Input_Tki, Input_Cas are all in the active sheet and Dim/Set in code above this block.
Tkm is double and Mix_Tkm is a Dim/Set range as well.
The Error 1004 kicks out in the first line of the Do Until ... Loop.

Since the syntax doesn't seem to be causing any issues in the IsEmpty(Range("Input_Cas")(i)) = True I'm not sure why that same syntax doesn't work below it. All cells referenced contain numerical values as well, just in case that is relevant.

I love you wonderful people and thanks for your help in advance.

Edit: The Dim/Set for the ranges are

Dim Input_Cas As Range
        Set Input_Cas = Worksheets("Calculations").Range("A2:A51")
Dim Input_Conc As Range
        Set Input_Conc = Worksheets("Calculations").Range("C2:C51")
Dim Input_Tki As Range
        Set Input_Tki = Worksheets("Calculations").Range("G2:G51")

Please show the "DIm/Set in code above this block" - Range("Input_Cas") would be appropriate for a named range, not a range variable. — BigBen
if the ranges are variables set earlier in the code, then they are either ranges or strings holding addresses. If range objects: Input_Cas(i) is all you need, if string addresses remove the "": Range(Input_Cas)(i) — Scott Craner
@bigben I didn't even know that Sumproduct was a thing. Dang I think that's what I want to do haha. I added the dim/set as an edit to my question — East
Yeah you just need Input_Cas(i) and similar... but just use SUMPRODUCT. — BigBen

East East · Accepted Answer · 2020-04-16T19:38:18

As suggested I am posting the answer to self for folks from the future.

I was attempting to recreate the Sumproduct function from excel within VBA by making a loop. What I was attempting was more effectively done in excel. I didn't know Sumproduct was a thing and it does exactly what I was attempting.

Thanks to Bigben for providing the solution I couldn't find for myself.

Error 1004 when referencing specific cells within a defined range

1 Answers