Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDIRECT with Sheet Level Defined Name | Excel Worksheet Functions | |||
use functions on defined multiple range | Excel Worksheet Functions | |||
defined sheet formula to vb code | Excel Discussion (Misc queries) | |||
Changing Multiple Defined Names At Once? | Excel Discussion (Misc queries) | |||
Using non defined names from another sheet | Excel Worksheet Functions |