VBA For each loop, type mismatch

Question

I have an issue where I get the type mismatch error when running my code, I'm trying to store an output of a calculation into an array using the 'For each' loop to loop through the range of cells that hold the value id like to multiply by a static value and then store.

How do I adjust the code to prevent the type mismatch error for the array GrossHS()?

Sub NetWeightDist()

Dim mltplier As Double
Dim SumGW As Long
Dim GrossHS() As Variant
Dim lngth As Long
Dim Netrng As Range
Dim NetCl As Range

lngth = Cells(Rows.Count, 9).End(xlUp).Row

SumGW = Application.WorksheetFunction.Sum(Range("L2:L1000"))
mltplier = Worksheets("Sheet1").Range("Q2").Value / SumGW

Set Netrng = Worksheets("Sheet1").Range("L2:L" & lngth)

ReDim GrossHS(1 To lngth)
For Each NetCl In Netrng

GrossHS() = mltplier * NetCl.Value
Next

End Sub

Answer 1

Put the column L values into an array and iterate that

Option Explicit
Sub NetWeightDist()

    Dim ws As Worksheet
    Dim mltplier As Double, lastrow As Long, i As Long
    Dim SumGW As Long, arGrossHS, arNet
    
    Set ws = ActiveSheet
    With ws
        lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row
        arNet = .Range("L2:L" & lastrow).Value2
        ReDim arGrossHS(1 To UBound(arNet))
         
        SumGW = Application.WorksheetFunction.Sum(arNet)
        mltplier = Sheets("Sheet1").Range("Q2") / SumGW
        MsgBox "Items=" & UBound(arNet) & " Sum=" & SumGW & " Multiplier=" & mltplier
        
        For i = 1 To UBound(arNet)
             arGrossHS(i) = mltplier * arNet(i, 1)
        Next
        
    End With 
End Sub