I don'T know how to code in VBA

[Pwel]

Hi, I previously posted my problem in another section but I didn't fin
any answer to solve my problem as I want... here is my previous post :

My problem is that I have a sheet with thousands of lines like those (
used "_" to align my column because space are not working) :

CLIENTS PRODUCT QTY TOTAL

Roger __ product1 __ 2 __ 080$
Roger __ product1 __ 3 __ 120$
Lynda __ product1 __ 1 __ 040$
Lynda __ product2 __ 1 __ 050$
Lynda __ product3 __ 1 __ 040$
Lynda __ product3 __ 2 __ 080$

and I need to add quantity and total for each time the client and th
product is the same to obtain something like this :

CLIENTS PRODUCT QTY TOTAL

Roger __ product1 __ 5 __ 200$
Lynda __ product1 __ 1 __ 040$
Lynda __ product2 __ 1 __ 050$
Lynda __ product3 __ 3 __ 120$

Now I think that making a macro in VBA could be the best thing to d
that...but I'm not good in VBA...I know C and PHP but not reall
VBA...anyway here is my code (it doesn't work) so if someone can tel
me what's wrong and how to fix it please!
(Sorry I didn't find a way to make multiple spaces to align my cod
correctly).

Dim i As Integer
Dim qty As Integer
Dim total As Integer

qty = 0
total = 0

For i = 1 To 100
If Cells(A, i) = Cells(A, i + 1) Then
If Cells(B, i) = Cells(B, i + 1) Then
'Updates the quantity
qty = Cells(C, i) + Cells(C, i + 1)
Cells(C, i) = qty
'Updates total
total = Cells(D, i) + Cells(D, i + 1)
Cells(D, i) = total

'On delete row i+1
Rows("i+1:i+1").Select
Selection.Delete Shift:=c1Up
End If
Else
End If
Next

 

[kraljb]

You cannot use cells(A, i) like that, it is trying to treat A as a
variable...

Cells(i,1) would actually refer you to column A, row i
Cells(i,2) would refer you to column B, row i
and so on and so forth

 

[RB Smissaert]

I hardly ever use them, but I have a feeling that a simple
Pivot table will do this all for you without any coding.
Just look in the Excel help for Pivot table.

RBS

 

[Guest]

No need for code. The data you have is ideal fo doing a pivot table. Select
data pivot table. Follow the wizard. Place the Name and Product in the right
column and the Quantity and Price in the Data section. Once created you can
drag and drop things to your hearts content to make it look just right... Add
a format to make it fancy and you are good to go...

 

[Bob Phillips]

Sub Test()
Dim amt1 As Double
Dim amt2 As Double
Dim i As Long

For i = Cells(Rows.Count, "A").End(xlUp).Row To 3 Step -1
If Cells(i, "A").Value = Cells(i - 1, "A").Value And _
Cells(i, "B").Value = Cells(i - 1, "B").Value Then
amt1 = amt1 + Cells(i, "C").Value
amt2 = amt2 + Cells(i, "D").Value
Rows(i).Delete
Else
Cells(i, "C").Value = Cells(i, "C").Value + amt1
Cells(i, "D").Value = Cells(i, "D").Value + amt2
amt1 = 0
amt2 = 0
End If
Next i

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Pwel]

Thank you very much Bob Phillips!

It works fine, this is exactly what I needed :