SwissBoy2098
06-08-2010, 03:13 AM
Does anyone know why I'm getting a 1004 run-time error? ("application defined or object defined")
Dim i As Integer
Dim EnergyArray As String
Dim MaterialsArray As String
Dim WaterArray As String
Dim EnergyCount As Integer
Dim MaterialsCount As Integer
Dim WaterCount As Integer
EnergyArray = "=MEDIAN('Portfolio Companies'!"
MaterialsArray = "=MEDIAN('Portfolio Companies'!"
WaterArray = "=MEDIAN('Portfolio Companies'!"
i = 4
Do Until Worksheets("Portfolio Companies").Cells(i, 2).Value = ""
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Energy") Then
EnergyArray = EnergyArray & "'Portfolio Companies'!G" & i & ","
EnergyCount = EnergyCount + 1
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Materials") Then
MaterialsArray = MaterialsArray & "'Portfolio Companies'!G" & i & ","
MaterialsCount = MaterialsCount + 1
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Water") Then
WaterArray = WaterArray & "'Portfolio Companies'!G" & i & ","
WaterCount = WaterCount + 1
End If
i = i + 1
Loop
EnergyArray = Left(EnergyArray, Len(EnergyArray) - 1)
EnergyArray = EnergyArray & ")"
MaterialsArray = Left(MaterialsArray, Len(MaterialsArray) - 1)
MaterialsArray = MaterialsArray & ")"
WaterArray = Left(WaterArray, Len(WaterArray) - 1)
WaterArray = WaterArray & ")"
Worksheets("VBA").Cells(3, 8) = EnergyArray
Worksheets("VBA").Cells(4, 8) = MaterialsArray
Worksheets("VBA").Cells(5, 8) = WaterArray
Worksheets("VBA").Cells(3, 9) = EnergyCount
Worksheets("VBA").Cells(4, 9) = MaterialsCount
Worksheets("VBA").Cells(5, 9) = WaterCount
Also, I realize that this is probably the worst way to get a MEDIAN of certain categories but I usually don't work with VBA and that's the first thing that came to my mind...
THANKS!
Dim i As Integer
Dim EnergyArray As String
Dim MaterialsArray As String
Dim WaterArray As String
Dim EnergyCount As Integer
Dim MaterialsCount As Integer
Dim WaterCount As Integer
EnergyArray = "=MEDIAN('Portfolio Companies'!"
MaterialsArray = "=MEDIAN('Portfolio Companies'!"
WaterArray = "=MEDIAN('Portfolio Companies'!"
i = 4
Do Until Worksheets("Portfolio Companies").Cells(i, 2).Value = ""
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Energy") Then
EnergyArray = EnergyArray & "'Portfolio Companies'!G" & i & ","
EnergyCount = EnergyCount + 1
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Materials") Then
MaterialsArray = MaterialsArray & "'Portfolio Companies'!G" & i & ","
MaterialsCount = MaterialsCount + 1
End If
If (Worksheets("Portfolio Companies").Cells(i, 3).Value = "Water") Then
WaterArray = WaterArray & "'Portfolio Companies'!G" & i & ","
WaterCount = WaterCount + 1
End If
i = i + 1
Loop
EnergyArray = Left(EnergyArray, Len(EnergyArray) - 1)
EnergyArray = EnergyArray & ")"
MaterialsArray = Left(MaterialsArray, Len(MaterialsArray) - 1)
MaterialsArray = MaterialsArray & ")"
WaterArray = Left(WaterArray, Len(WaterArray) - 1)
WaterArray = WaterArray & ")"
Worksheets("VBA").Cells(3, 8) = EnergyArray
Worksheets("VBA").Cells(4, 8) = MaterialsArray
Worksheets("VBA").Cells(5, 8) = WaterArray
Worksheets("VBA").Cells(3, 9) = EnergyCount
Worksheets("VBA").Cells(4, 9) = MaterialsCount
Worksheets("VBA").Cells(5, 9) = WaterCount
Also, I realize that this is probably the worst way to get a MEDIAN of certain categories but I usually don't work with VBA and that's the first thing that came to my mind...
THANKS!