• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 165
  • Last Modified:

Embedding formula in VBA

Private Sub cmdResultsShadedCells_Click()
ActiveSheet.Range("A3,A7,A8").Select
Selection.Interior.Color = RGB(255, 0, 0)
Range("A9").Select
'Here I would like to sum only those cells colored red;
Range("A1").Select
End Sub

Open in new window


I'm trying to sum all cells colored red in my code above.
0
Frank Freese
Asked:
Frank Freese
  • 7
  • 5
2 Solutions
 
Martin LissOlder than dirtCommented:
You should use Select as little as possible so I combined your lines 2 and 3
Private Sub cmdResultsShadedCells_Click()
Dim cel As Range
Dim dblTot As Double
ActiveSheet.Range("A3,A7,A8").Interior.Color = RGB(255, 0, 0)
Range("A9").Select
'Here I would like to sum only those cells colored red;
For Each cel In ActiveSheet.UsedRange
    If cel.Interior.Color = RGB(255, 0, 0) Then
        dblTot = dblTot + cel.Value
    End If
Next
Range("A1").Select
End Sub

Open in new window

0
 
Frank FreeseAuthor Commented:
Martin,
Welcome back from you cruise in January. I'm getting close to finishing my project.
The answer I get for dblTotal is what I am expecting. The error I'm receiving is "Type mismatch"
Since dblTotal is defined as double, what formatting for the cell should I use. I think that is why I'm getting this error?
0
 
Martin LissOlder than dirtCommented:
Try changing line 9 to

dblTot = dblTot + CDbl(cel.Value)
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Frank FreeseAuthor Commented:
same problem
0
 
Frank FreeseAuthor Commented:
I meant to mention that the answer appears in A9
0
 
Martin LissOlder than dirtCommented:
Can you attach your workbook, or at least one that demonstrates the problem?
0
 
Rgonzo1971Commented:
Hi,

Maybe one of the values is not numeric

Private Sub cmdResultsShadedCells_Click()
Dim cel As Range
Dim dblTot As Double
ActiveSheet.Range("A3,A7,A8").Interior.Color = RGB(255, 0, 0)
'Here I would like to sum only those cells colored red;
For Each cel In ActiveSheet.UsedRange
    If IsNumeric(cel.Value) And cel.Interior.Color = RGB(255, 0, 0) Then
        dblTot = dblTot + cel.Value
    End If
Next
Range("A9").Value = dblTot
Range("A1").Select
End Sub

Open in new window

but maybe you only want to sum in A9 : A3,A7 and A8
you could use
Range("A9").Formula = "=SUM(A3,A7,A8)"

Open in new window

or if you do not want a formula
Range("A9").Value= Evaluate("SUM(A3,A7,A8)")

Open in new window

Regards
0
 
Frank FreeseAuthor Commented:
Thanks for all the help. Last night I read about the .Value and .Formula for the Range and being late as it was thought to try it this morning.  Tried the .Formula first and problem solved.
Then I read these responses and it felt good to know that I was moving in the right direction.
Thanks and I'll share the points here between the two of you. The continual effort of all has value to me.
0
 
Frank FreeseAuthor Commented:
Thank you very much
0
 
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2013
0
 
Frank FreeseAuthor Commented:
Martin,
I dislike asking this of you. I need to post a question on some work you did on that big workbook last fall. It has to deal with when I select a category then list the topics under that category. Your code works fine on the first line item but not on any other additions to topics when I select a different category. The information is being pulled from the wrong column and I don't see the problem. This is a new workbook since I completed the big one several weeks ago.
0
 
Martin LissOlder than dirtCommented:
I'll be happy to help. Post a new question and I (or someone) will give it a shot.
0
 
Frank FreeseAuthor Commented:
Give me a few minutes - maybe, just maybe I found the problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now