Need UDF help; cycling through various sheets using VBA name property
I haven't been able to get the normal worksheet functions to do what I need, so I think I'm stuck making my own. I have 7 sheets, each with identical tables. On an eight sheet, I have another table (same format) in which I need to generate unweighted averages of the relative cell from each source page. I would have done this with an array formula, but cells without values in the source tables are =N/A (and I need to keep them that way for graphing purposes), and apparently isna and iserror don't work in array formulas across multiple pages. So, in cell C3 of my roll-up table, I need to cycle through the values of cells C3 on each source worksheet, and if the value is something other than N/A, include that value in my unweighted average. Where I'm stuck is how to reference the other sheets in the UDF: it stops execution on the "choose" line, without giving any errors Public Function Consolidated(SourceCell As Range) As Variant Dim userSheet As Worksheet UseRow = SourceCell.Row 'I'm the only one using this, so I know I'll only have one cell selected UseCol = SourceCell.Column For i = 1 To 7 Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19") 'I also tried: 'ActivePull = Choose(i, Sheet9, Sheet12, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19) ' without quotes didn't work either 'Set userSheet = ActivePull 'do something here like : 'if userSheet.Range(UseCol & UseRow).value < NA then ' TotalValue = TotalValue + userSheet.Range(UseCol & UseRow).value ' DivCount = DivCount+1 'end if ' then divide TotalValue/DivCount to get the average MsgBox userSheet.Range("A2").Value 'it never gets to this line Next End Function Any and all help is welcome and appreciated- Thanks, Keith R XL97 |
Need UDF help; cycling through various sheets using VBA name property
Your problem is with this line:
Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19") Change it to: userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19") The problem is that you were using the Set keyword, which would be useful if you wanted to make userSheet an array. In this case, you just want it to be a single variable. Let me know if you have questions. Mark --- Mark Bigelow mjbigelow at hotmail dot com http://hm.imperialoiltx.com *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Need UDF help; cycling through various sheets using VBA name property
Public Function Consolidated() As Variant
Application.Volatile Dim userSheet As Worksheet Dim i As Long Dim UseRow As Long Dim UseCol As Long Dim TotalValue As Double Dim DivCount As Double Dim rng As Range Set rng = Application.Caller UseRow = rng.Row UseCol = rng.Column For i = 1 To 7 Set userSheet = Worksheets(Choose(i, "Sheet9", "Sheet12", _ "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19")) ' do something here like : If Not IsError(userSheet.Cells(UseRow, UseCol).Value) Then If Not IsEmpty(userSheet.Cells(UseRow, UseCol).Value) Then If IsNumeric(userSheet.Cells(UseRow, UseCol).Value) Then TotalValue = TotalValue + _ userSheet.Cells(UseRow, UseCol).Value DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then Consolidated = TotalValue / DivCount Else Consolidated = 0 End If End Function You want to reference the cell containing the formula, but this would cause a circular reference error. Since you don't but references in the argument of the cell, excel doesn't know what cells this formula is dependent on, so you need to use Application.Volatile to make it calculate on each recalculation. Tested in Excel 97. Regards, Tom Ogilvy Keith R wrote in message news:01c35095$a8cd93a0$476a1bac@PC12001... I haven't been able to get the normal worksheet functions to do what I need, so I think I'm stuck making my own. I have 7 sheets, each with identical tables. On an eight sheet, I have another table (same format) in which I need to generate unweighted averages of the relative cell from each source page. I would have done this with an array formula, but cells without values in the source tables are =N/A (and I need to keep them that way for graphing purposes), and apparently isna and iserror don't work in array formulas across multiple pages. So, in cell C3 of my roll-up table, I need to cycle through the values of cells C3 on each source worksheet, and if the value is something other than N/A, include that value in my unweighted average. Where I'm stuck is how to reference the other sheets in the UDF: it stops execution on the "choose" line, without giving any errors Public Function Consolidated(SourceCell As Range) As Variant Dim userSheet As Worksheet UseRow = SourceCell.Row 'I'm the only one using this, so I know I'll only have one cell selected UseCol = SourceCell.Column For i = 1 To 7 Set userSheet = Choose(i, "Sheet9", "Sheet12", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19") 'I also tried: 'ActivePull = Choose(i, Sheet9, Sheet12, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19) ' without quotes didn't work either 'Set userSheet = ActivePull 'do something here like : 'if userSheet.Range(UseCol & UseRow).value < NA then ' TotalValue = TotalValue + userSheet.Range(UseCol & UseRow).value ' DivCount = DivCount+1 'end if ' then divide TotalValue/DivCount to get the average MsgBox userSheet.Range("A2").Value 'it never gets to this line Next End Function Any and all help is welcome and appreciated- Thanks, Keith R XL97 |
Need UDF help; cycling through various sheets using VBA name property
Thanks to Tom, Bob, and Mark for your responses. I had forgotten about the
application.volatile issue, so I went ahead and decided to pass the source ranges directly. Here's the final product, in case anyone is interested :) Keith R XL97 ================================================== ====== Public Function Consolidated(SC1 As Range, SC2 As Range, SC3 As Range, _ SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range) As Variant 'each input range will be a single cell Dim i As Long Dim ws As Range Dim TotalValue As Double Dim DivCount As Double For i = 1 To 7 Set ws = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7)) If Not IsError(ws.Value) Then If Not IsEmpty(ws.Value) Then If IsNumeric(ws.Value) Then TotalValue = TotalValue + ws.Value DivCount = DivCount + 1 End If End If End If Next If DivCount 0 Then Consolidated = TotalValue / DivCount Else Consolidated = CVErr(xlErrNA) End If End Function |
All times are GMT +1. The time now is 04:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com