VBA code and sum by colour decimal places issue

[CVN55]

Hi
I've tried various VBA codes but cannot get decimal places showing. The file I've uploaded shows my VBA code, and my excel sheet where the column is showing to decimal points as .00, but it should be what shows in the left hand column. Can anyone help please? My VBA code shows 'double' and not 'long' which seems to be the main issue for folk.

Thank you in advance,
Catherine

 

[Joe4]

Can you post a small sample of your data (first removing any sensitive information, if necessary), and your VBA code?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[CVN55]

Hi - hopefully this works? This shows my VBA code, and my excel sheet where the column is showing to decimal points as .00, but it should be what shows in the left hand column.

 

[Joe4]

I am failing to see/understand what decimal points have to do with counting cells by color.
If there is an issue with decimal points not showing in your second column, that has nothing to do with this SumByColour code (BTW, you want CountByColour, not SumByColour).

The issue would be with how that second column is being populated.
Do you have code for that?
Can you post that code for us to see (and please post the code, not a picture of it)?
See here for how to post your VBA code: How to Post Your VBA Code

 

[CVN55]

Yes, it's the second column, and it's definitely the sum of the colours, hence the decimals. Hopefully this code comes through as requested:

Function SumByColour(CellColor As Range, rRange As Range, Optional num_digits = 2)
  Dim cl As Range
  Dim cSum As Double
  Dim ColIndex As Integer
  ColIndex = CellColor.Interior.ColorIndex
  For Each cl In rRange
    If cl.Interior.ColorIndex = ColIndex Then
      cSum = cSum + cl.Value
    End If
  Next cl
  SumByColor = WorksheetFunction.Round(cSum, num_digits)
End Function

 

[Fluff]

I'm surptised that functions returns anything other than 0 as you have miss-spelt the name of the function at the end.

 

[Joe4]

You have a typo in your last line of code. You have:

SumByColor = WorksheetFunction.Round(cSum, num_digits)

and it should be:

SumByColour = WorksheetFunction.Round(cSum, num_digits)

When I try out your corrected code, it seems to work just fine for me:

In the example you posted, you also have the arguments reversed. The single cell is the first argument, and the larger range you are checking is the second.
And you are using that other function name.

Makes me think you are calling some other function you attempted.

 

[CVN55]

You have a typo in your last line of code. You have:

SumByColor = WorksheetFunction.Round(cSum, num_digits)

and it should be:

SumByColour = WorksheetFunction.Round(cSum, num_digits)

When I try out your corrected code, it seems to work just fine for me:

View attachment 118253

In the example you posted, you also have the arguments reversed. The single cell is the first argument, and the larger range you are checking is the second.
And you are using that other function name.

Makes me think you are calling some other function you attempted.

Thank you so much @Joe4, I started afresh and this worked after making these corrections, really appreciate your help with this.