Thread: Suming a column to another worksheet

Hello everyone,

I am new to Excel programming, however, I am a software developer. I thought a forum would be best place to get my question answered. Here's what I want to do:

On the first worksheet I have a column (multiple rows) that can have one of 23 names entered in it. In the column next to that (also multiple rows), a dollar amount is entered.

On the next worksheet I have all the names on different rows, with their total amount. I'm assuming I would need to code some sort of macro or something to take the amounts for each person on the first worksheet and add it to their individual totals on the second worksheet, correct?

If someone could point me to either a tutorial that would demonstrate how to do this, or maybe a snippet of code, that would be great.

Thanks!

Chris

Hi there and welcome to the wild, wooly world of Excel programming. Here's
some code that should accomplish what you want. I've made a couple of
assumptions for the code below to work so you'll need to do this also.
(1) Create a range name called "CurrAmountsList" for the two columns (names
and amounts) in your "current" dollar amounts list (note that the range can
have more rows than the data currently within the range; it's just handy to
have an identifiable range name that holds the maximum amount of names and
amounts that you expect to be there)
(2) Creat a range name called "AccAmountsList" for the two columns in your
accumulated dollar amounts list (this is presumably the names of all the
folks for whom you are accumulating bucks).
P.S. If you don't know how to set up a range name, drop a reply post here
and I'll tell you how.

Sub AddBucks()
'Adds dollar amounts for individuals on Sheet 1 to the accumulated
'totals for those individuals on Sheet2

Dim i As Integer
Dim CurrName As String, CurrBucks As Single
Dim FindName As Integer
Dim NameCount As Integer

On Error Resume Next

NameCount = Application.WorksheetFunction.CountA(Range("CurrAmountsList"))

For i = 1 To NameCount
'Select the first individual in the current amount list and set
their name
'and current dollar value
CurrName = Range("CurrAmountsList").Cells(i, 1).Value
CurrBucks = Range("CurrAmountsList").Cells(i, 2).Value
'Determine where this individual is in the accumulated totals list
FindName = Application.WorksheetFunction.Match(CurrName,
Range("AccAmountsList").Columns(1), 0)
Range("AccAmountsList").Cells(FindName, 2).Value = CurrBucks +
Range("AccAmountsList").Cells(FindName, 2).Value
Next i

End Sub

"CRYM" wrote:

>
> Hello everyone,
>
> I am new to Excel programming, however, I am a software developer. I
> thought a forum would be best place to get my question answered. Here's
> what I want to do:
>
> On the first worksheet I have a column (multiple rows) that can have
> one of 23 names entered in it. In the column next to that (also
> multiple rows), a dollar amount is entered.
>
> On the next worksheet I have all the names on different rows, with
> their total amount. I'm assuming I would need to code some sort of
> macro or something to take the amounts for each person on the first
> worksheet and add it to their individual totals on the second
> worksheet, correct?
>
> If someone could point me to either a tutorial that would demonstrate
> how to do this, or maybe a snippet of code, that would be great.
>
> Thanks!
>
> Chris
>
>
> --
> CRYM
> ------------------------------------------------------------------------
> CRYM's Profile: http://www.excelforum.com/member.php...o&userid=35287
> View this thread: http://www.excelforum.com/showthread...hreadid=550711
>
>