How to create a function using references

[Freeman]

I am trying to create a VB function to calculate some data on between 2 excel
workbooks. I have got this to work using a "sum if" statement with nested
"if" within the cells of the workbook. WB 1 has data in tables and WB 2 has
the calculated totals. Here is the basic layout of the 2 WB.

WB1:

Date Account# Amount($) Cleared
Jan 30, 06 123456 20.00 No
Jan 31, 06 145678 50.00 Yes


WB2:

Date Total
Jan 20, 06 "location of the sum if statement"
Jan 21, 06 "location of the sum if statement"

The above works fine for now, but I want to create a function that I can
call to automate the process. I have a couple of problems doing this:

1) How do I pass a reference to a range of cells to a function
ie: $F$2:$F$12
2) How do I extract the beginning and end of that range and make variables
equal to those figures.
ie: x = $F$2
y = $F$12
3) I want to loop from "x" to "y" and during that loop use "x" to lookup the
cell data so I can use it to compare values and update a total using "if"
statements.

Really my biggest problem here is the issue of using pointers in between
Excel and VB. Does anyone have any ideas?? What are the limits of doing
this??

 

[Bob Phillips]

1) How do I pass a reference to a range of cells to a function
ie: $F$2:$F$12

Just use a range like

Function myFunc(rng As Range)
....

and call like

=myFunc($F$2:$F$12)

2) How do I extract the beginning and end of that range and make variables
equal to those figures.
ie: x = $F$2
y = $F$12

Set cell1 = rng(1, 1)
Set cell2 = rng(rng.Count)

3) I want to loop from "x" to "y" and during that loop use "x" to lookup the
cell data so I can use it to compare values and update a total using "if"
statements.

But it easier to do

For Each cell in rng
... do something with cell which will be each cel in the range
Next cell

Really my biggest problem here is the issue of using pointers in between
Excel and VB. Does anyone have any ideas?? What are the limits of doing
this??

Don't really understand what you mean.