Write value to dynamic array VBA

Question

Code worked fine with fixed dim array but when I have added line to Redim to make Results array dynamic the code will not allow results to be added to array coming up with "Type Mismatch" error. I am sure a simple fix, have played around but can I see it..

GP (Gross Profit) Range of values picked up form single column range GPRange and Price Change its from single row PriceRange).

Option Explicit
Sub CalcVolTable()
'Macro to produce table that shows relationship between price and volume  change
Dim PriceChangeAr As Variant, GPAr As Variant
Dim GPList, GPNum, PriceIndex, PriceChNum As Integer
Dim GP As Double


With ThisWorkbook

' Read all PriceChanges into a 1-dimensional array
    PriceChangeAr = (.Worksheets("Results").Range("PriceRange").Value2)

' Read all GP range into a 1-dimensional array
        GPAr =     Application.Transpose(.Worksheets("Results").Range("GPRange").Value)

'Clear Previous Results
    Range("VolTable").ClearContents
    Range("Output").Select

    'Set up Results Array
    'Dim VolResultsAr(1 To 8, 1 To 7) As Variant - this worked before I tried to make array dynamic
    Dim VolResultsAr As Variant
    PriceChNum = UBound(PriceChangeAr, 2)
    GPNum = UBound(GPAr)
    ReDim VolResults(1 To GPNum, 1 To PriceChNum) As Variant

    For GPList = LBound(GPAr) To UBound(GPAr)

        GP = GPAr(GPList)

        'Set Cost per Unit value to get right GP in calc
        Range("CostPerUnit").Value = 100 * (1 - GP)
            ' Now loop through each pricechange
            For PriceIndex = LBound(PriceChangeAr, 2) To UBound(PriceChangeAr, 2)

                'Reset Price and Vol adjt cell to zero
                Range("ChPrice") = 0
                Range("Chvol") = 0

            'enter new Price Cahnge value
                Range("ChPrice").Value = PriceChangeAr(1, PriceIndex)

                'Use goal seek to calc vol chage req'd to bring GP back to same preset value
                Range("GP").GoalSeek Goal:=GP, ChangingCell:=Range("ChVol")

                'Writes result to cells in table in spreadsheet
                Range("Output").Offset(GPList - 1, PriceIndex - 1).Value = Range("ChVol").Value

                'CODE FALLING DOWN HERE - TRYING TO WRITE EACH RESULT INTO ARRAY
                VolResultsAr(GPList, PriceIndex) = Range("ChVol").Value


            Next PriceIndex

        Next GPList

    Range("Output2").Resize(UBound(VolResultsAr, 1), UBound(VolResultsAr, 2)) = VolResultsAr

    Range("Output").Select


End With

MsgBox "done!"

End Sub

It would help if you gave the range addresses for all those named ranges. — QHarr
– QHarr, Commented Aug 4, 2018 at 7:49

QHarr · Accepted Answer · 2018-08-04 08:01:22Z

1

Assuming the arrays need to match you need to ReDim VolResultsAr

ReDim VolResults(1 To GPNum, 1 To PriceChNum) As Variant
ReDim VolResultsAr(1 To GPNum, 1 To PriceChNum) As Variant

And for lines like:

Dim GPList, GPNum, PriceIndex, PriceChNum As Integer .

Only PriceChNum is Integer, the others are variant. Is that what you intended? And Integer should be replaced with Long to avoid potential overflow

answered Aug 4, 2018 at 8:01

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Redimming the same array was the issue. Note to self - keep variable names minimal and simple, and check consistent naming throughout!! The GPList, GPNum etc are integers as they are holding the size of the array - have Dimmed as Long rather than Variant. All good now. Thanks for help.

Collectives™ on Stack Overflow

Write value to dynamic array VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related