VBA Macro passing value to another worksheet

[yirga]

Please help me to correct the code. the programs run and gives the correct value but it over write the previous values of other cells. please see at the bottom the input. thanks.

Sub SumItems_V4XX()
Dim w1 As Worksheet, w2 As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, j As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
a = w1.Range("A1:H" & w1.Range("A" & Rows.Count).End(xlUp).Row)
ReDim o(1 To UBound(a, 1), 1 To 8)
For i = LBound(a, 1) To UBound(a, 1)
If a(i, 1) <> "house1" Then
j = j + 1
o(j, 4) = a(i, 2)
o(j, 6) = a(i, 4)
o(j, 8) = a(i, 6)


ElseIf a(i, 1) = "house1" And a(i + 1, 1) = "house2" Then
j = j + 1
o(j, 4) = a(i, 2) + a(i + 1, 2)
o(j, 6) = a(i, 3) + a(i + 1, 4)
o(j, 8) = a(i, 4) + a(i + 1, 6)
i = i + 1
Else
j = j + 1
o(j, 4) = a(i, 2)
o(j, 6) = a(i, 4)
o(j, 8) = a(i, 6)
End If
Next i
With w2
.Columns(1).ClearContents
.Range("A1").Resize(UBound(o, 1), UBound(o, 2)) = o
.Columns(1).AutoFit
.Activate
End With
Application.ScreenUpdating = True
End Sub

Sheet1 - input

A	B	C	D	E	F	G	H
arange	cars	88		88		88
arange	buses	76		89		34
arange	house1	89		90		89
arange	house2	45		91		45
arange	cars	34		92		55
arange	buses	33		93		45
arange	house1	78		94		78
arange	house2	45		95		45
arange	cars	55		96		55
arange	buses	5		97		8
arange	house1	55		98		55
arange	house2	55		99		55
arange	cars	34		100		22
arange	buses	12		101		11
arange	house1	55		102		55
arange	house2	55		103		55

<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

Sheet2 - the result should be expected as the following - the code above should process sheet1 C, E, G to pass the value to Sheet2 D, F, H with out affecting or clearing other columns. I appreciate you for sharing your precious time and knowledge with me. thanks.

A	B	C	D	E	F	G	H
Location	Price	Old Bal1	23	Old Bal2	44	Old Bal3	67
Location	Price	Old Bal2	51	Old Bal3	47	Old Bal4	116
Location	Price	Old Bal3	27	Old Bal4	48	Old Bal5	71
Location	Price	Old Bal4	57	Old Bal5	50	Old Bal6	122
Location	Price	Old Bal5	30	Old Bal6	51	Old Bal7	74
Location	Price	Old Bal6	63	Old Bal7	53	Old Bal8	128
Location	Price	Old Bal7	33	Old Bal8	54	Old Bal9	77
Location	Price	Old Bal8	34	Old Bal9	55	Old Bal10	78

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[williamgowtama]

try to use .value in the end of o(j,1) and a(i,1). Like this >>>o(j,1).value



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


Please Give me like if my post helps


Need my help further?


id.linkedin.com/in/williamgowtama


Thank you :D