Solved: New to VBA

[gambit]

Hello all,
This is my first foray into VBA and don't know where to start. Here's what I need to do, psuedocode-wise:

If column B <> 0
For each record in column A
Sum column H
End For loop

Copy columns A-G, and sum of column H to "COPY" worksheet
Endif

The worksheet will have tracking numbers in column A, with the charges for the tracking numbers in column H. Each record in column A will correspond to a different type of charge for the tracking number; package charge, fuel surcharge, customs clearance charges, etc. These need to be summed and copied into one line so the charges can be imported into our ERP system.

Can anyone recommend a tutorial, or tutorials, that will cover these topics?

TIA

[Simon Lloyd]

If you want to sum column H for every iteration wont you just get the same result each time as the contents of H won't be changing, or did you mean sum A-G in H for each value that isnt 0 in column B?

[gambit]

Column A Column H

1Z1232345 32.50
1Z1232345 7.50
1Z1232345 10.50

For each line in column A, column H needs to be summed.

[Simon Lloyd]

Sorry but i still dont understand you!, your showing column A as a mix of numbers and letters are you adding it to column H or are you summing all cells in between? How did you arrive at those odd figures for each of the exact same entries in column A?

Mixed cells (i.e numbers and letters) are regarded by excel as text so will not be part of a calculation.

[Simon Lloyd]

Still not understanding what you want i've knocked this up, give it a try and see if its what you wanted.
[VBA]Sub Sum_N_Move()
Dim Rng As Range, MyCell As Range, sM As Long
Set Rng = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
If MyCell <> 0 Then
sM = Application.WorksheetFunction.Sum(Range(MyCell.Offset(0, -1).Address & ":" & MyCell.Offset(0, 5).Address))
MyCell.Offset(0, 6) = sM
MyCell.EntireRow.Copy Destination:=Sheets("Copy").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next MyCell
End Sub[/VBA]

[Bob Phillips]

Just use SUMIF

=SUMIF(A:A,A2,H:H)

[gambit]

Sorry for the confusion...I was using column when I should be using cell

The values in H cells will be summed into one value, based on the number of matching values in A cells. Cells A-G will not change, only cell H will change, then all the cells will need to be copied to a new worksheet.

The for-each loop (maybe not available in VBA) will sum cell H for each value in column A that is the same. The cell H sum total could be copied to a new cell, along with cells A-G (the values in these cells do not change, and will be the same on each line where cell A matches.)

Hopefully that is a better explanation.

[gambit]

I think a visual would be better.

Old data:

Col A........Col B.......Col H

1Z12345...Info.........3500
1Z12345...Info.........3500
1Z34567...Info.........1250
1Z98013...Info.........1000
1Z37894...Info.........5000
1Z37894...Info.........2500

Col A........Col B.......Col H


New data:


Col A........Col B.......Col H

1Z12345...Info.........7000
1Z34567...Info.........1250
1Z98013...Info.........1000
1Z37894...Info.........7500

[Bob Phillips]

Just use SUMIF

=SUMIF(Sheet1!A:A,Sheet2!A2,Sheet1!H:H)

[Simon Lloyd]

I think a visual would be better.

Old data:

Col A........Col B.......Col H

1Z12345...Info.........3500
1Z12345...Info.........3500
1Z34567...Info.........1250
1Z98013...Info.........1000
1Z37894...Info.........5000
1Z37894...Info.........2500

Col A........Col B.......Col H


New data:


Col A........Col B.......Col H

1Z12345...Info.........7000
1Z34567...Info.........1250
1Z98013...Info.........1000
1Z37894...Info.........7500

If i wasn't confused before i am now, a workbook would help!

Just use SUMIF

=SUMIF(Sheet1!A:A,Sheet2!A2,Sheet1!H:H)

, you should try xld's suggestion....he is our resident god when it comes to worksheet functions!!

Bob, i was thinking, if i didn't get you noticed the third post would have been a SUMPRODUCT

[gambit]

xld...this is what is being returned using sumif:


Old data:

Col A........Col B.......Col H

1Z12345...Info.........3500
1Z12345...Info.........3500
1Z34567...Info.........1250
1Z98013...Info.........1000
1Z37894...Info.........5000
1Z37894...Info.........2500

New data:

Col H

7000
7000
1250
1000
7500
7500

Also, I'm surely missing something, but it is only copying column H to the new worksheet. The new sheet needs one line for each tracking number, so 1Z12345 having two lines with 3500 on the old sheet each would be one line with 7000 on the new sheet.

[gambit]

Thanks Simon! Your help is much appreciated. Is there a way to attach a workbook for an example?

[Bob Phillips]

What I am suggesting gambit is to either copy the unique values in column A to a new sheet, or use formulae to grab them, and then use the second formula I gave.

Like this

[GTO]

Greetings Gambit,

Below the Quick Reply box, press the <Go Advanced> button. In the new window, you will see a <Manage Attachments> button.

Hope that helps,

Mark

[Simon Lloyd]

Maybe there is a call for sumproduct =SUMPRODUCT((Sheet1!H2:H20)*(Sheet1!A2:H20=A2))

Remember for sumproduct (although Bob can disprove this probably) you ranges span the same rows, so if you change the above formula to say H100 then you must do it for both sides of the formula.

[gambit]

xld...The nested vlookup got the job done. Thanks for your help!

[Simon Lloyd]

If your query has been resolved please mark the thread solved by going to THREAD TOOLS>MARK THREAD SOLVED

[gambit]

I was looking for a way to mark it solved earlier but didn't see the link. All I see in thread tools is "Show Printable Version", "Email this Page", and "Unsubscribe from this Thread". My status shows as logged in.

[CHatmaker]

Wouldn't a pivot table work well here?