Hi

Im trying to figure out how to do a pretty basic operation with VBA.
Let say we have a spread sheet with the range A1:A4 that contain a 1 or
a 2, and a range B1:B4 containg any number. What I need to do is this:
If the number in a cell in row A =1 add the number in the B row of the
same column with any others that meet the critieria.

I will show the nested function that accomplishes this task for
clarification: "{=sum(if(A1:A3=1,B1:B3))}"

the reason I can't use the nested function is that this is but a small
portion of a very big operation im trying create.

Here is what I have in VBA, the thing I don't understand is how to make
it add up the numbers under number that meet the criteria:

Function Maybe(A As Range, B As Range)

Dim K As Integer

For Each A In A

If A = 1 Then
K = K + B?????????
End If
Next

Maybe = K

End Function


---
Message posted from http://www.ExcelForum.com/

Can you use

=SUMIF(A1:A4,1,B1:B4)

???

In article ,
ksnapp wrote:

Hi

Im trying to figure out how to do a pretty basic operation with VBA.
Let say we have a spread sheet with the range A1:A4 that contain a 1 or
a 2, and a range B1:B4 containg any number. What I need to do is this:
If the number in a cell in row A =1 add the number in the B row of the
same column with any others that meet the critieria.

I will show the nested function that accomplishes this task for
clarification: "{=sum(if(A1:A3=1,B1:B3))}"

the reason I can't use the nested function is that this is but a small
portion of a very big operation im trying create.

Here is what I have in VBA, the thing I don't understand is how to make
it add up the numbers under number that meet the criteria:

Function Maybe(A As Range, B As Range)

Dim K As Integer

For Each A In A

If A = 1 Then
K = K + B?????????
End If
Next

Maybe = K

End Function


---
Message posted from http://www.ExcelForum.com/

The problem with using sumif is that this is only a small part of wha
is very big nested function. The issue shown is the part I can'
figure out on my own

--
Message posted from http://www.ExcelForum.com

You can't nest UDFs deeper than 7 either, so making the function a UDF
won't help you.

Perhaps if you gave some idea of what your big nested function involved
someone could help find a better solution...

In article ,
ksnapp wrote:

The problem with using sumif is that this is only a small part of what
is very big nested function. The issue shown is the part I can't
figure out on my own.

The end product of my nested function is this, Here is a version of i
without specific cell refrences"

"{=Normin([CELL REFERENCE THAT CONTAINS THE PROBABILITY IM LOOKIN
FOR],Average(if([ROW REFFERENCE THAT CONTAINS DAY NUMBERS]=[CEL
REFFERENCE THAT CONTAINS THE DAY NUMBER IM LOOKING FOR],[ROW REFFERENC
THAT CONTAINS THE ACTUAL DATA I WANT ANALYZED])),Stdev((if([RO
REFFERENCE THAT CONTAINS DAY NUMBERS]=[CELL REFFERENCE THAT CONTAIN
THE DAY NUMBER IM LOOKING FOR],[ROW REFFERENCE THAT CONTAINS THE ACTUA
DATA I WANT ANALYZED])))}"


Or this may be easier on the eyes:

"{=Norminv(A1, average(if(B:B=A2,C:C)),Stdev(if(B:B=A2,C:C)))}"

where A1 contains a probability, A2 contains a day number (1-7), wher
B:B countains a bunch of day numbers, and C:C countains the actua
numbers to crunch.

The reason I want a UDF so bad is that all of the cell refrences are t
a different workbook and entering this formula for 126 different set
of data is tedious and error prone, even with all the pastin
techniques that usually take the work out of my day.


Let me extend my thanks to everybody who has commented on my problem.

thank yo

--
Message posted from http://www.ExcelForum.com

I may be misunderstanding your situation, but it sounds to me that
instead of a UDF, you're more in need of defining range names. For
instance:

Choose Insert/Name/Define

Name in workbook Refers to:
p_1 [Workbook2.xls]Sheet1!A1
daynumber1 [Workbook2.xls]Sheet1!A2
daynumbers [Workbook2.xls]Sheet1!B:B
crunch [Workbook2.xls]Sheet1!C:C

then your formula becomes:

{=NormInv(p_1, AVERAGE(IF(daynumbers = daynumber1, crunch),
STDEV(IF(daynumbers = daynumber1, crunch))}






In article ,
ksnapp wrote:

The reason I want a UDF so bad is that all of the cell refrences are to
a different workbook and entering this formula for 126 different sets
of data is tedious and error prone, even with all the pasting
techniques that usually take the work out of my day.