# VBA coding

Closed
modi366 Posts 3 Registration date Friday April 10, 2015 Status Member Last seen April 10, 2015 - Apr 10, 2015 at 10:30 PM
modi366 Posts 3 Registration date Friday April 10, 2015 Status Member Last seen April 10, 2015 - Apr 10, 2015 at 10:46 PM
Hello everybody. I am a newbie when it comes to VBA. I hoping your help to check the following code and give me a solution.

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, 3) + a(i + 1, 2)
o(j, 6) = a(i, 5) + a(i + 1, 4)
o(j, 8) = a(i, 7) + a(i + 1, 6)
i = i + 1
Else
j = j + 1
o(j, 4) = a(i, 3)
o(j, 6) = a(i, 5)
o(j, 8) = a(i, 7)
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

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
Price Bal1 23 44 67
Price Bal2 51 116 88
Price Bal3 27 48 71
Price Bal4 57 l5 50 Price Bal5 30 51 74
Price Bal6 63 53 128
Price Bal7 33 54 77
Price Old Bal8 34 55 78

## 1 response

modi366 Posts 3 Registration date Friday April 10, 2015 Status Member Last seen April 10, 2015
Apr 10, 2015 at 10:38 PM
Sorry for the inconvenience.
sheet1
B C D E F G H
house2 26 47 70
Test4 27 48 71
house1 28 49 72
house2 29 50 73
Test9 30 51 74
house1 31 52 75
house2 32 53 76
Test12 33 54 77
Test13 34 55 78

Sheet2
A B C D E F G H
Item type date 23 44 67
Item type date 51 93 139
Item type date 27 48 71
Item type date 57 99 145
Item type date 30 51 74
Item type date 63 105 151
Item type date 33 54 77
Item type date 34 55 78
modi366 Posts 3 Registration date Friday April 10, 2015 Status Member Last seen April 10, 2015
Apr 10, 2015 at 10:46 PM
I am not sure if it help:
on Sheet1
C, E,and G or 3, 5, and 7 have the data

on sheet 2
4, 6, 8 are receivers, data on 1, 2,3 must be not overwritten.