Call User Defined Function to work with ActiveCell.Column

[Vanish29]

Good Morning
Task:
Color set of cells, and then sum cells in active column, based on background of cell, then output to specific location.

I found a set of functions that allow me to Count and Sum Cells, based on Color.
I have set up the functions and confirmed they work separately.
Unfortunately, I cannot get everything to now play nice in my userform. I have tried a few configurations, but to no avail.

Here is the code

Dim A1 As Range
Set A1 = Range("A1")
If CheckBox1.Value = True Then
StatTotal = AutoRedist.ComboBox1.Text
Cells.Find(What:=StatTotal, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
RStat = SumByColor(A1, (ActiveCell.Column))
Cells(lastRow, ActiveCell.Column).Offset(1, 0).Value = RStat
ElseIf ...

Here is the code for the functions, just in case

Function SumByColor(CellColor As Range, rRange As Range)
Dim cSum As Long
Dim ColIndex As Integer
ColIndex = CellColor.Interior.ColorIndex
For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
        cSum = WorksheetFunction.Sum(cl, cSum)
    End If
Next cl
SumByColor = cSum


End Function
Function CountByColor(CellColor As Range, CountRange As Range)
Dim ICol As Integer
Dim TCell As Range
ICol = CellColor.Interior.ColorIndex
For Each TCell In CountRange
If ICol = TCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next TCell
End Function

The issue seems to be in this line.

RStat = SumByColor(A1, (ActiveCell.Column))

When calling this function in the spreadsheet itself, it would look like this

=SumByColor(A1,D:D)

All help is appreciated.
Thanks

 

[Yongle]

The clue is in what the function is expecting

Function CountByColor(CellColor As Range, CountRange As Range) it is looking for 2 ranges, but your code is supplying one Range and one Integer

Set A1 = Range("A1")
RStat = SumByColor(A1, (ActiveCell.Column))

ActiveCell.Column returns the column number (an integer, not a range)


Try

RStat = SumByColor(A1, ActiveCell.EntireColumn)

 

[Vanish29]

Ah that would explain it.
That fixed that issue, and now it calls just fine.
The issue now is the SumByColor now consistently returns a 0, even though the function works normally if called directly in the worksheet as outlined above.
However, the CountByColor works both in the UserForm and directly in the worksheet.
Are you able to see other issues with the code that would cause it to not work with the UserForm?

 

[Yongle]

The function works correctly when called within VBA (see below)
- ranges are your issue one way or another
- ensure VBA is told the SHEET for each range by qualifying the range with the sheet name (or variable)

Function CountByColor(CellColor As Range, CountRange As Range)
    Dim ICol As Integer, TCell As Range
    ICol = CellColor.Interior.ColorIndex
    For Each TCell In CountRange
        If ICol = TCell.Interior.ColorIndex Then CountByColor = CountByColor + 1
    Next TCell
End Function

Sub HowMany()
    Dim Cel As Range, Rng1 As Range, Rng2 As Range, Apples As Worksheet, Oranges As Worksheet
    Set Apples = Sheets("Apples")
    Set Oranges = Sheets("Oranges")
    Set Cel = Apples.Range("A1")
    Set Rng1 = Apples.Range("D2:D2000")
    Set Rng2 = Oranges.Range("D2:D2000")
    Debug.Print CountByColor(Cel, Rng1)
    Debug.Print CountByColor(Cel, Rng2)
End Sub

See images below, where Debug.Print correctly returns 14 and 6 to the Iimmediate Window - and matches what the formula is returning in D1

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​				14​	=CountByColor(A1,D2:D2000)
2​
3​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​

Sheet: Apples

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​
1​				6​	=CountByColor(Apples!A1,D2:D2000)
2​
3​					1​
4​
5​
6​
7​
8​
9​
10​
11​
12​
13​
14​
15​
16​
17​
18​

Sheet: Oranges

 

[Vanish29]

I am not certain I understand
Why would the CountByColor function work when it is called with the same parameters (shown below)

RStat = CountByColor(A1, ActiveCell.EntireColumn)

But the SumByColor function does not?

RStat = SumByColor(A1, ActiveCell.EntireColumn)

That suggests that it is not a range issue, but maybe in the function itself?

 

[Vanish29]

Hey Yongle,
I am confused as to why we would need to specify which sheet, especially when default is Active Sheet. In addition, the ActiveCell should overrule that and designate active sheet as current sheet.
However, as a good student, I did what you suggested, and it is still returning 0
Here is the updated code:

Dim ws As Worksheet
Set ws = Sheets("Redistribution")
Set A1 = ws.Range("A1")
If CheckBox1.Value = True Then
StatTotal = AutoRedist.ComboBox1.Text
Cells.Find(What:=StatTotal, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
Set StatCol = ws.Range(ActiveCell.EntireColumn, ActiveCell.EntireColumn)
RStat = SumByColor(A1, StatCol)
Cells(lastRow, ActiveCell.Column).Offset(1, 0).Value = RStat...

 

[Yongle]

Why would the CountByColor function work when it is called with the same parameters but the SumByColor function does not?
That suggests that it is not a range issue, but maybe in the function itself?

Making count and sum range an entire column requires VBA to loop through more than a million rows
How many rows are acually in use?
Consider getting VBA to determine the last row when setting the range

Or use something like this which assumes that data never extends below row 10000

Sub Test()
    Dim rng As Range, A1 As Range
    Set A1 = Range("A1")
    Set rng = ActiveSheet.Range("A1:A[COLOR=#ff0000]10000[/COLOR]").Offset(, ActiveCell.Column - 1)
    MsgBox "Count= " & CountByColor(A1, rng)
    MsgBox "Sum = " & SumByColor(A1, rng)
End Sub

 

[Vanish29]

Interesting.
I got the code to work with that offset portion.
Here is the final code

Set A1 = ws.Range("A1")
Dim CalcCol As Range
If CheckBox1.Value = True Then
StatTotal = AutoRedist.ComboBox1.Text
Cells.Find(What:=StatTotal, after:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:=False _
        , SearchFormat:=True).Activate
Set StatCol = Range(ActiveCell.EntireColumn, ActiveCell.EntireColumn)
Set CalcCol = ws.Range("A" & lastRow).Offset(, ActiveCell.Column - 1)
RStat = SumByColor(A1, CalcCol)

I had a lastrow previously defined so I added it here, per your suggestion.
Much thanks for your help.
While it is not important, as the problem has been solved, but why would ActiveCell.EntireColumn work for this?
Based on all the literature, this should have been the optimal range to use.