Runtime Error 1004 with working accross worksheets & string concatenation

[SwissBoy2098]

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!

[Bob Phillips]

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("
MaterialsArray = "=MEDIAN("
WaterArray = "=MEDIAN("

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

[SwissBoy2098]

Thanks!
I've been working on this project for days now, the lack of sleep is getting obvious :banghead: