Run Time Error '13' Type mismatch
 

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

Run Time Error '13' Type mismatch
 

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 .

--
Regards;
Rob
------------------------------------------------------------------------
"David Adamson" wrote in message
...
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

Run Time Error '13' Type mismatch
 

Thanks