Excel VBA replace/substitue module

Question

I'm using a custom function called ColorFunction which I found on the internet to sum the values in a range of cells if the background colour is the same as another cell.

This is what is in the cell that shows the total.

=ColorFunction($AE$3,$B$3:$W$3,TRUE)

And the ColorFunction is as below.

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = WorksheetFunction.SUM(rCell, vResult)
        End If
    Next rCell
Else
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
    Next rCell
End If
ColorFunction = vResult
End Function

This all works fine. However some of the values I'm using there is an * in them after the number value. For my other cells that I sum up these values in I use the following.

=SUMPRODUCT(VALUE(0&SUBSTITUTE(B3:W3,"*","")))

Is there any way I can do this with the ColorFunction to ignore the * in the cells when summing up the cells with a colour in them.

Thanks in advance.

tigeravatar · Accepted Answer · 2013-08-16 15:45:13Z

2

vResult = vResult + Val(0 & Replace(rCell.Text, "*", vbNullString))

Alternately, if the first part of the cell is the number, you could just use Val

vResult = vResult + Val(rCell.Text)

answered Aug 16, 2013 at 15:45

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Ian Woodhead Over a year ago

Cheers. I used the following down voteunaccept vResult = vResult + Val(0 & Replace(rCell.Text, "*", vbNullString)) and it works great

Our Man in Bananas · Accepted Answer · 2013-08-16 15:46:00Z

0

you can modify the code as below by adding the REPLACE function (vba.Replace(rCell, "*", ""):

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = WorksheetFunction.SUM(replace(rCell,"*",""), vResult)
        End If
    Next rCell
Else
    For Each rCell In rRange
        If rCell.Interior.ColorIndex = lCol Then
            vResult = 1 + vResult
        End If
    Next rCell
End If
ColorFunction = vResult
End Function

answered Aug 16, 2013 at 15:46

Our Man in Bananas

6,00525 gold badges104 silver badges158 bronze badges

Collectives™ on Stack Overflow

Excel VBA replace/substitue module

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related