Data Manipulation in VB
Posted by Mark on September 10, 2001 12:38 PM
can anybody suggest where do I start with the following VBA stuff:
if values in ABC row 1 = values in ABC row 2 = values in ABC row 3 (see FIGURE 1) then write values ABCD of the first row to a new workbook with the E (5+5+5) and F (6+6+6) to appear as a sum of the 3 rows.
The first copied row should look as follows:
A B C D E F
1 1 2 3 4 15 18
...second row copied would read:
A B C D E F
2 2 3 4 5 6 7
If values are not equal then copy the entire rows into the new sheet.
A B C D E F
1 1 2 3 4 5 6
2 1 2 3 4 5 6
3 1 2 3 4 5 6
4 2 3 4 5 6 7
5 3 4 5 6 7 8
FIGURE 1
any help would be appreciated very much...
Regards
M.
Posted by Barrie Davidson on September 10, 2001 2:53 PM
Mark, an interesting code to write. Try the following:
Sub Summerize()
' Written by Barrie Davidson
Dim A, B, C, D, E, F
Range("A1").Select
Do Until Selection.Value = ""
A = ActiveCell.Value
B = ActiveCell.Offset(0, 1).Value
C = ActiveCell.Offset(0, 2).Value
D = ActiveCell.Offset(0, 3).Value
E = 0
F = 0
Do Until ActiveCell.Value <> A And ActiveCell.Offset(0, 1).Value <> B And _
ActiveCell.Offset(0, 2).Value <> C And ActiveCell.Offset(0, 3).Value <> D
E = E + ActiveCell.Offset(0, 4).Value
F = F + ActiveCell.Offset(0, 5).Value
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Sheet2").Activate
ActiveCell.Value = A
Range("B1").Value = B
ActiveCell.Offset(0, 1).Value = B
ActiveCell.Offset(0, 2).Value = C
ActiveCell.Offset(0, 3).Value = D
ActiveCell.Offset(0, 4).Value = E
ActiveCell.Offset(0, 5).Value = F
ActiveCell.Offset(1, 0).Select
Sheets("Sheet1").Select
Loop
End Sub
Note that the data being copied is in "Sheet1" and is being copied to "Sheet2" so make any changes to these names as required. If you have any problems just let me know.
Regards,
BarrieBarrie Davidson
