Call User Defined Function to work with ActiveCell.Column

Vanish29 · Apr 23, 2019

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

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

Code:

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.

Code:

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

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

Code:

=SumByColor(A1,D:D)

All help is appreciated.
Thanks

Yongle · Apr 24, 2019

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

Code:

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

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

Try

Code:

RStat = SumByColor(A1, ActiveCell.EntireColumn)

Vanish29 · Apr 24, 2019

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 · Apr 24, 2019

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)

Code:

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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td=bgcolor:#FFFF00][/td][td][/td][td][/td][td]

14

[/td][td] =CountByColor(A1,D2:D2000)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2