software

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 quot;sum ifquot; statement with nested
quot;ifquot; 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 YesWB2:

Date Total
Jan 20, 06 quot;location of the sum if statementquot;
Jan 21, 06 quot;location of the sum if statementquot;

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 quot;xquot; to quot;yquot; and during that loop use quot;xquot; to lookup the
cell data so I can use it to compare values and update a total using quot;ifquot;
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??


quot;Freemanquot; gt; wrote in message
...

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

Just use a range like

Function myFunc(rng As Range)
....

and call like

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

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

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

gt; 3) I want to loop from quot;xquot; to quot;yquot; and during that loop use quot;xquot; to lookup
the
gt; cell data so I can use it to compare values and update a total using quot;ifquot;
gt; 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

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

Don't really understand what you mean.