Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF is updateing cells on another sheet with count from current sheet.
Hi All,
First, thanks for your time: I wrote a UDF function that counts background colors. It takes a cell argument with the background color that I want to count. I work out the range in the function because the top of the sheet is like a report (headings, legend, and color count that kind of stuff); the data is pasted below the top part. I know the first row and find the last row. Here is the issue. There are 2 sheets in the work book that use this function. When I hit Atl - Ctrl - Shift - F9 it counts the colors but puts the count the in both sheets instead of each sheet having it's own count of the colors that are on it. I pasted the code below. Please help I'm about to start pulling my hair out. 'counts colored cells in given range by color Function CountProjects(RngColor As Range) As Integer Dim Srow As Long 'Start Row Dim Erow As Long 'End Row Dim Crow As Long 'Current Row Dim Cll As Range 'range of cells Dim Clr As Long 'color Dim Rng As Range 'range of cells to look at for color Dim xlCalc As XlCalculation '''''''''''''''''''''''''''''''''' Dim savScrnUD As Boolean 'for speeding up calculations ' savScrnUD = Application.ScreenUpdating 'only ' Application.ScreenUpdating = False ' ' xlCalc = Application.Calculation ' ' Application.Calculation = xlCalculationManual '''''''''''''''''''''''''''''''''' On Error GoTo CalcBack 'Error Handler With ActiveSheet .DisplayPageBreaks = False Erow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Find last record of data End With Clr = RngColor.Range("A1").Interior.Color 'color = selected cell color If ActiveSheet.Name = "AFESummaryRpt" Then Srow = 13 'set start row for AFESummaryRpt ' Sheets("AFESummaryRpt").Select ElseIf ActiveSheet.Name = "AlignBudgetReport" Then Srow = 9 ' set start row for AlignBudgetReport ' Sheets("AlignBudgetReport").Select End If Set Rng = Range("A" & Srow & ":" & "O" & Erow) 'set cell range for whichever sheet is active For Each Cll In Rng 'loop thru cells in range If Cll.Interior.Color = Clr Then 'if cell color matchs cell in range CountProjects = CountProjects + 1 'add one to count of colors End If Next Cll CalcBack: If Err Then MsgBox Err.Description 'If error messagebox error description Application.Calculation = xlCalc 'Set speed up options back to normal Application.ScreenUpdating = savScrnUD 'Set speed up options back to normal End Function |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF is updateing cells on another sheet with count from current sheet.
Instead of referencing ActiveSheet (either explicitly or impltly), you should reference Applicaion.Caller.Worksheet
HTH -- AP '-------------------------------------- 'counts colored cells in given range by color Function CountProjects(RngColor As Range) As Integer Dim Srow As Long 'Start Row Dim Erow As Long 'End Row Dim Crow As Long 'Current Row Dim Cll As Range 'range of cells Dim Clr As Long 'color Dim Rng As Range 'range of cells to look at for Color Dim xlCalc As XlCalculation '''''''''''''''''''''''''''''''''' Dim savScrnUD As Boolean 'for speeding up calculations savScrnUD = Application.ScreenUpdating 'only Application.ScreenUpdating = False ' ' xlCalc = Application.Calculation ' ' Application.Calculation = xlCalculationManual '''''''''''''''''''''''''''''''''' On Error GoTo CalcBack 'Error Handler With Application.Caller.Worksheet .DisplayPageBreaks = False Erow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Find last record of data End With Clr = RngColor.Range("A1").Interior.Color 'color =selected cell color If Application.Caller.Worksheet.Name = "AFESummaryRpt" Then Srow = 13 'set startrow for AFESummaryRpt ' Sheets("AFESummaryRpt").Select ElseIf Application.Caller.Worksheet.Name = "AlignBudgetReport" Then Srow = 9 ' set start row for AlignBudgetReport ' Sheets("AlignBudgetReport").Select End If Set Rng = Application.Caller.Worksheet.Range("A" & Srow & ":" & "O" & Erow) 'set cell range for whichever sheet is active For Each Cll In Rng 'loop thru cells in range If Cll.Interior.Color = Clr Then 'if cell color matchs cell in range CountProjects = CountProjects + 1 'add one to count of colors End If Next Cll CalcBack: If Err Then MsgBox Err.Description 'If error messagebox errorDescription Application.Calculation = xlCalc 'Set speed up options back to normal Application.ScreenUpdating = savScrnUD 'Set speed up options back to normal End Function '------------------------------------------------------------------------------------ "John" a écrit dans le message de oups.com... Hi All, First, thanks for your time: I wrote a UDF function that counts background colors. It takes a cell argument with the background color that I want to count. I work out the range in the function because the top of the sheet is like a report (headings, legend, and color count that kind of stuff); the data is pasted below the top part. I know the first row and find the last row. Here is the issue. There are 2 sheets in the work book that use this function. When I hit Atl - Ctrl - Shift - F9 it counts the colors but puts the count the in both sheets instead of each sheet having it's own count of the colors that are on it. I pasted the code below. Please help I'm about to start pulling my hair out. 'counts colored cells in given range by color Function CountProjects(RngColor As Range) As Integer Dim Srow As Long 'Start Row Dim Erow As Long 'End Row Dim Crow As Long 'Current Row Dim Cll As Range 'range of cells Dim Clr As Long 'color Dim Rng As Range 'range of cells to look at for color Dim xlCalc As XlCalculation '''''''''''''''''''''''''''''''''' Dim savScrnUD As Boolean 'for speeding up calculations ' savScrnUD = Application.ScreenUpdating 'only ' Application.ScreenUpdating = False ' ' xlCalc = Application.Calculation ' ' Application.Calculation = xlCalculationManual '''''''''''''''''''''''''''''''''' On Error GoTo CalcBack 'Error Handler With ActiveSheet .DisplayPageBreaks = False Erow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Find last record of data End With Clr = RngColor.Range("A1").Interior.Color 'color = selected cell color If ActiveSheet.Name = "AFESummaryRpt" Then Srow = 13 'set start row for AFESummaryRpt ' Sheets("AFESummaryRpt").Select ElseIf ActiveSheet.Name = "AlignBudgetReport" Then Srow = 9 ' set start row for AlignBudgetReport ' Sheets("AlignBudgetReport").Select End If Set Rng = Range("A" & Srow & ":" & "O" & Erow) 'set cell range for whichever sheet is active For Each Cll In Rng 'loop thru cells in range If Cll.Interior.Color = Clr Then 'if cell color matchs cell in range CountProjects = CountProjects + 1 'add one to count of colors End If Next Cll CalcBack: If Err Then MsgBox Err.Description 'If error messagebox error description Application.Calculation = xlCalc 'Set speed up options back to normal Application.ScreenUpdating = savScrnUD 'Set speed up options back to normal End Function |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF is updateing cells on another sheet with count from current sheet.
Also posted in public.excel
"John" wrote in message oups.com... Hi All, First, thanks for your time: I wrote a UDF function that counts background colors. It takes a cell argument with the background color that I want to count. I work out the range in the function because the top of the sheet is like a report (headings, legend, and color count that kind of stuff); the data is pasted below the top part. I know the first row and find the last row. Here is the issue. There are 2 sheets in the work book that use this function. When I hit Atl - Ctrl - Shift - F9 it counts the colors but puts the count the in both sheets instead of each sheet having it's own count of the colors that are on it. I pasted the code below. Please help I'm about to start pulling my hair out. 'counts colored cells in given range by color Function CountProjects(RngColor As Range) As Integer Dim Srow As Long 'Start Row Dim Erow As Long 'End Row Dim Crow As Long 'Current Row Dim Cll As Range 'range of cells Dim Clr As Long 'color Dim Rng As Range 'range of cells to look at for color Dim xlCalc As XlCalculation '''''''''''''''''''''''''''''''''' Dim savScrnUD As Boolean 'for speeding up calculations ' savScrnUD = Application.ScreenUpdating 'only ' Application.ScreenUpdating = False ' ' xlCalc = Application.Calculation ' ' Application.Calculation = xlCalculationManual '''''''''''''''''''''''''''''''''' On Error GoTo CalcBack 'Error Handler With ActiveSheet .DisplayPageBreaks = False Erow = .Cells(.Rows.Count, "A").End(xlUp).Row 'Find last record of data End With Clr = RngColor.Range("A1").Interior.Color 'color = selected cell color If ActiveSheet.Name = "AFESummaryRpt" Then Srow = 13 'set start row for AFESummaryRpt ' Sheets("AFESummaryRpt").Select ElseIf ActiveSheet.Name = "AlignBudgetReport" Then Srow = 9 ' set start row for AlignBudgetReport ' Sheets("AlignBudgetReport").Select End If Set Rng = Range("A" & Srow & ":" & "O" & Erow) 'set cell range for whichever sheet is active For Each Cll In Rng 'loop thru cells in range If Cll.Interior.Color = Clr Then 'if cell color matchs cell in range CountProjects = CountProjects + 1 'add one to count of colors End If Next Cll CalcBack: If Err Then MsgBox Err.Description 'If error messagebox error description Application.Calculation = xlCalc 'Set speed up options back to normal Application.ScreenUpdating = savScrnUD 'Set speed up options back to normal End Function |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF is updateing cells on another sheet with count from current sheet.
Thanks alot I think that got it.
John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto "copy and paste" individual cells from various sheets into one sheet ?? | Excel Discussion (Misc queries) | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
Formula to copy rng of cells where (value is met) to anther sheet | Excel Worksheet Functions | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) |