Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
John
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Jim Cone
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
John
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto "copy and paste" individual cells from various sheets into one sheet ?? [email protected] Excel Discussion (Misc queries) 2 March 1st 06 04:19 AM
Combining data from cells from several excel sheets to a new sheet Rik Excel Discussion (Misc queries) 4 February 22nd 06 10:16 AM
Formula to copy rng of cells where (value is met) to anther sheet Exit Advantage Excel Worksheet Functions 3 November 12th 05 03:59 AM
Can I count how many grey-filled cells are in a row ? AnthonyG Excel Worksheet Functions 1 February 4th 05 11:08 AM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 07:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"