Using a defined formula in multiple sheet
 

Hi,

I would like to define a general function to use in multiple sheets. The
formula is a lookup funcion that looks within the sheet. Is it possible to
let it only look at the current sheet and not let it use the same values in
other sheets?

Here is a sample of my function:
-----------------------
Function Alpha(A, B)

Application.Volatile (True)

Reset_Arrays

Worksheets(B).Select

m = 1

For i = 6 To 350
If Left(Cells(i, 15), 1) = "Z" Or Left(Cells(i, 15), 1) = "X" Or
Left(Cells(i, 15), 2) = "TV" Or Left(Cells(i, 15), 2) = "VP" Or
Left(Cells(i, 15), 2) = "VU" Or Left(Cells(i, 15), 2) = "A-" Then
Current(m, 1, B) = Cells(i, 16)
Current(m, 2, B) = Cells(i, 17)
Current(m, 3, B) = Cells(i, 18)
m = m + 1
End If
Next

For i = 1 To m
Alpha = Alpha + Current(i, A, B)
Next

End Function
-------------------------------

When I use it, it will show up the same values in any worksheet this formula
is defined. I hope this makes sense. It is really driving me crazy.

Thank you in advance

Roel Balbin
Accounting/Sales Support
Teragren, LLC

Using a defined formula in multiple sheet
 

Hi
problem is that you hardcoded worksheet B in this function. Why don't
you sue the cell range to be processed as parameter in your function?

--
Regards
Frank Kabel
Frankfurt, Germany

"Roel" schrieb im Newsbeitrag
...
Hi,

I would like to define a general function to use in multiple
sheets. The
formula is a lookup funcion that looks within the sheet. Is it
possible to
let it only look at the current sheet and not let it use the same
values in
other sheets?

Here is a sample of my function:
-----------------------
Function Alpha(A, B)

Application.Volatile (True)

Reset_Arrays

Worksheets(B).Select

m = 1

For i = 6 To 350
If Left(Cells(i, 15), 1) = "Z" Or Left(Cells(i, 15), 1) = "X" Or
Left(Cells(i, 15), 2) = "TV" Or Left(Cells(i, 15), 2) = "VP" Or
Left(Cells(i, 15), 2) = "VU" Or Left(Cells(i, 15), 2) = "A-" Then
Current(m, 1, B) = Cells(i, 16)
Current(m, 2, B) = Cells(i, 17)
Current(m, 3, B) = Cells(i, 18)
m = m + 1
End If
Next

For i = 1 To m
Alpha = Alpha + Current(i, A, B)
Next

End Function
-------------------------------

When I use it, it will show up the same values in any worksheet this
formula
is defined. I hope this makes sense. It is really driving me crazy.

Thank you in advance

Roel Balbin
Accounting/Sales Support
Teragren, LLC