Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Embedding formula in VBA

Posted on 2014-03-16
13
Medium Priority
?
160 Views
Last Modified: 2014-03-25
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
Comment
Question by:Frank Freese
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 600 total points
ID: 39933166
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
 

Author Comment

by:Frank Freese
ID: 39933184
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39933210
Try changing line 9 to

dblTot = dblTot + CDbl(cel.Value)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Frank Freese
ID: 39933216
same problem
0
 

Author Comment

by:Frank Freese
ID: 39933230
I meant to mention that the answer appears in A9
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39933295
Can you attach your workbook, or at least one that demonstrates the problem?
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 1400 total points
ID: 39933788
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
 

Author Comment

by:Frank Freese
ID: 39934415
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
 

Author Closing Comment

by:Frank Freese
ID: 39934417
Thank you very much
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39934493
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
 

Author Comment

by:Frank Freese
ID: 39953815
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39953826
I'll be happy to help. Post a new question and I (or someone) will give it a shot.
0
 

Author Comment

by:Frank Freese
ID: 39953867
Give me a few minutes - maybe, just maybe I found the problem
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question