Thread: VBA Code help - first timer

Hello all,

I am trying to write a UDF for Excel, and I haven't worked with VBA programming before. Here is what I want to do:

Two columns next to each other (for example, a and b, but not necessarily those exact columns). Column A is filled with "distribution" amounts, are long decimal numbers (ie .0084840). Column B is filled with matching dollar amounts (ie 10.75). So, cells A1 and B1 are a "record" or match, A2 and B2, all the way down to An and Bn (n being any number). What I want to do is take the distribution amounts, divide it by the dollar amount (ie A1/B1), add one to the fraction, then multiply all records together. In other words:

(1+(A1/B1))*(1+(A2/B2))*(1+(A3/B3))*.........*(1+(An/Bn)).

Any help? post back here or email me at poots1101@gmail.com

Thanks in advance!

Not NECESSARY to use a UDF - this formula will do the job. If your data is
A1:B13, ctrl/shift/enter this:

=PRODUCT((1+A1:A13/B1:B13))

Bob Umlas
Excel MVP

"Poots" <Poots.1qdfaf_1118340318.0272@excelforum-nospam.com> wrote in
message news:Poots.1qdfaf_1118340318.0272@excelforum-nospam.com...
>
> Hello all,
>
> I am trying to write a UDF for Excel, and I haven't worked with VBA
> programming before. Here is what I want to do:
>
> Two columns next to each other (for example, a and b, but not
> necessarily those exact columns). Column A is filled with
> "distribution" amounts, are long decimal numbers (ie .0084840). Column
> B is filled with matching dollar amounts (ie 10.75). So, cells A1 and
> B1 are a "record" or match, A2 and B2, all the way down to An and Bn (n
> being any number). What I want to do is take the distribution amounts,
> divide it by the dollar amount (ie A1/B1), add one to the fraction,
> then multiply all records together. In other words:
>
> (1+(A1/B1))*(1+(A2/B2))*(1+(A3/B3))*.........*(1+(An/Bn)).
>
> Any help? post back here or email me at poots1101@gmail.com
>
> Thanks in advance!
>
>
> --
> Poots
> ------------------------------------------------------------------------
> Poots's Profile:
http://www.excelforum.com/member.php...o&userid=24154
> View this thread: http://www.excelforum.com/showthread...hreadid=377806
>

=SUMPRODUCT(1+A1:A10/B1:B10)

--
Regards,
Tom Ogilvy

"Poots" <Poots.1qdfaf_1118340318.0272@excelforum-nospam.com> wrote in
message news:Poots.1qdfaf_1118340318.0272@excelforum-nospam.com...
>
> Hello all,
>
> I am trying to write a UDF for Excel, and I haven't worked with VBA
> programming before. Here is what I want to do:
>
> Two columns next to each other (for example, a and b, but not
> necessarily those exact columns). Column A is filled with
> "distribution" amounts, are long decimal numbers (ie .0084840). Column
> B is filled with matching dollar amounts (ie 10.75). So, cells A1 and
> B1 are a "record" or match, A2 and B2, all the way down to An and Bn (n
> being any number). What I want to do is take the distribution amounts,
> divide it by the dollar amount (ie A1/B1), add one to the fraction,
> then multiply all records together. In other words:
>
> (1+(A1/B1))*(1+(A2/B2))*(1+(A3/B3))*.........*(1+(An/Bn)).
>
> Any help? post back here or email me at poots1101@gmail.com
>
> Thanks in advance!
>
>
> --
> Poots
> ------------------------------------------------------------------------
> Poots's Profile:
http://www.excelforum.com/member.php...o&userid=24154
> View this thread: http://www.excelforum.com/showthread...hreadid=377806
>