Relative VBA function

[Brian]

I've tried searching this forum, but can't find out how to do this.

I have a function that works correctly when the exact range is
specified. Now I want to copy my cell that uses this function to a big
table. How do I adjust the range call? Here is the code


Function myformula(a As Range, b As Range, c As Double,) As Variant

x = a.Columns.Count
y = b.Columns.Count

If x <> y Then
myformula = "Error"
Exit Function
Else

For t = 1 To x
total = total + a.Cells(1, t) / (1 + b.Cells(1, t)) ^ (c + (t - 1))
Next t

myformula = total
End If

End Function


a() is Sheet2!A:b11:az11
b() is Sheet3!A:b11:az11
c=.25

Next data entry point is in row 22 (i.e. 11 rows down) but I want to
copy "myformula" entered in sheet1!a1 to sheet1!a2. When I do, the
referenced range a() changes to row 12 (as I would expect). Can I make
the "myformula" range a() drop 11 rows each time i copy it down without
actually copying it down 11 rows then moving the formula. I need to
repeat this formula 1000 by 20 times.

Thanks

 

[Scott]

This isn't a problem with your macro, it's with how you use your
formula.

You should call your formula in a similar fashion to the following:

A1: = myformula(INDIRECT("Sheet2!AB" & ROW(A1)*11 & ":AZ" &
ROW(A1)*11), INDIRECT("Sheet3!AB" & ROW(A1)*11 & ":AZ" &
ROW(A1)*11),.25)

Note that you might have to make ROW(A1) in the above
(ROW(A1)-ROW($A$1)+1) if there is the risk of inserting rows... that
will make the formula more flexible. (Albeit somewhat larger) You can
also play with ADDRESS() to make it even more flexible.... but you
could end up with a messy looking formula. It all depends on how
robust you want to make it.

Scott

 

[Brian]

What a bonehead I am!

Thanks.

Brian