Defining new function with cell range parameter

I feel really dumb asking this, but 1) I've never had to do it before, and 2)
I really did try it myself using Help!

I want to define a function I can call on a spreadsheet that'll take a cell
range (columns across a row) as input, and then step through the range of
cells in the function doing various things to the individual cells, then
return the result into the cell where the function is called.

In the spreadsheet (say, row 20) I'd expect to have a cell with, say,
"=DBA(J20:T20)" and the function (DBA) would take the 11 cells and loop
through them doing some math; to keep it simple, assume adding them up (I
KNOW there is a function to do that!) and returning the value.

In another row the same function would process the cells from that row.

I'm missing a fundamental concept, e.g., referencing the cell range in the
function and stepping through it. I am really not this dumb!

Can anyone provide a sample as a starter?

Put this in a general module

Public function DBA(rng as Range)
Dim tot as Double, cell as Range
tot = 0
for each cell in rng
if isnumeric(cell) then
tot = tot + Cell.Value
end if
Next
DBA = tot
End Function


--
Regards,
Tom Ogilvy


"NormD" <[email protected]> wrote in message
news:[email protected]...
> I feel really dumb asking this, but 1) I've never had to do it before, and
2)
> I really did try it myself using Help!
>
> I want to define a function I can call on a spreadsheet that'll take a
cell
> range (columns across a row) as input, and then step through the range of
> cells in the function doing various things to the individual cells, then
> return the result into the cell where the function is called.
>
> In the spreadsheet (say, row 20) I'd expect to have a cell with, say,
> "=DBA(J20:T20)" and the function (DBA) would take the 11 cells and loop
> through them doing some math; to keep it simple, assume adding them up (I
> KNOW there is a function to do that!) and returning the value.
>
> In another row the same function would process the cells from that row.
>
> I'm missing a fundamental concept, e.g., referencing the cell range in the
> function and stepping through it. I am really not this dumb!
>
> Can anyone provide a sample as a starter?

Damned if it doesn't work! First time, too. Thanks, Tom.

"Tom Ogilvy" wrote:

> Put this in a general module
>
> Public function DBA(rng as Range)
> Dim tot as Double, cell as Range
> tot = 0
> for each cell in rng
> if isnumeric(cell) then
> tot = tot + Cell.Value
> end if
> Next
> DBA = tot
> End Function
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "NormD" <[email protected]> wrote in message
> news:[email protected]...
> > I feel really dumb asking this, but 1) I've never had to do it before, and
> 2)
> > I really did try it myself using Help!
> >
> > I want to define a function I can call on a spreadsheet that'll take a
> cell
> > range (columns across a row) as input, and then step through the range of
> > cells in the function doing various things to the individual cells, then
> > return the result into the cell where the function is called.
> >
> > In the spreadsheet (say, row 20) I'd expect to have a cell with, say,
> > "=DBA(J20:T20)" and the function (DBA) would take the 11 cells and loop
> > through them doing some math; to keep it simple, assume adding them up (I
> > KNOW there is a function to do that!) and returning the value.
> >
> > In another row the same function would process the cells from that row.
> >
> > I'm missing a fundamental concept, e.g., referencing the cell range in the
> > function and stepping through it. I am really not this dumb!
> >
> > Can anyone provide a sample as a starter?
>
>
>