Run Time Error '13' Type mismatch

[David Adamson]

This code was working fine unit I tried to divide 1 result by another.



I'm getting a Run Time Error '13' Type mismatch on the

Following line

Worksheets("Zone Summary").Cells(5 + c + (1 + (10 * x - 10)), 6) =
Data2/Data



I have tried to use 'Dim 'for the 'Data' but then I get a compile error
saying that it expects an array.



Any suggestions about fixing this up?


------------------


Sub SummariseProdStats()



'set number of Stages

For x = 1 To 7



Step = "Zone " & x



For c = 1 To 7



'Commodity 1

ReDim Data(0) ' area

Data(0) = 0

ReDim Data1(0) 'production

Data1(0) = 0

ReDim Data2(0) 'value

Data2(0) = 0



numrows = Worksheets("Zones Raw Data").UsedRange.Rows.Count - 8



For r = 1 To numrows



'Where Data you are testing and getting data from

With Worksheets("Zones Raw Data")

Zonerng = .Cells(8 + r, 1) '.Select

CommodArea = .Cells(8 + r, (c * 3) + 1)

CommodProd = .Cells(8 + r, (c * 3) + 2)

CommodValue = .Cells(8 + r, (c * 3) + 3)

End With



'Save Existing Data

ReDim Preserve Data(UBound(Data) + 1)

ReDim Preserve Data1(UBound(Data1) + 1)

ReDim Preserve Data2(UBound(Data2) + 1)





'Check to see if condition true or false

If Step = Zonerng Then

'add up area

Data(UBound(Data)) = CommodArea

Data(0) = Data(0) + CommodArea

'add up prod

Data1(UBound(Data1)) = CommodProd

Data1(0) = Data1(0) + CommodProd



'add up value

Data2(UBound(Data2)) = CommodValue

Data2(0) = Data2(0) + CommodValue





End If



Next r



'Now paste value to where you want them

Worksheets("Zone Summary").Cells(5 + c + (1 + (10 * x - 10)), 3) = Data

Worksheets("Zone Summary").Cells(5 + c + (1 + (10 * x - 10)), 4) = Data1

Worksheets("Zone Summary").Cells(5 + c + (1 + (10 * x - 10)), 5) = Data2

Worksheets("Zone Summary").Cells(5 + c + (1 + (10 * x - 10)), 6) =
Data2/Data



Next c



Next x



End Sub

 

[RWN]

You haven't subscripted your "Data" fields.
You've declared them as arrays but forgot to provide the subscripts when
you used them, hence VBA is expecting an array reference .

 

[David Adamson]

Thanks