Solved

How to creat an if_then statement using a formula to trigger the condtion from an excel spreadsheet

Posted on 2012-03-30
2
164 Views
Last Modified: 2012-04-07
I am trying to create an if then statement in vba  using the result of a formula in excel as the condition.  The formula in "F3" is as follows:  =IF('Bun Oven'!$C$2=0,(IF('Bun Oven'!$D$2>0,(IF('Bun Oven'!$D$2>(SUM('Bun Oven'!$D$3+(PRODUCT('Bun Oven'!$D$3,5%)))),"Actual speed is 5% OVER setpoint"," ")))))

The formula in "F4" is exactly the same as "F3"  other than it is looking for a value 5% less than the set point.


The vba code is as follows:
Sub Bun_Oven_Update_Report_Data()
'updating Bun Oven
IF NOT(ISEMPTY(Worksheets("Bun Oven")).Range("F3").Value OR IF NOT(ISEMPTY(Worksheets("Bun Oven")).Range("F4").Value  Then


Worksheets("Bun Oven").Range("E2:F8").Copy
Worksheets("Bun Report").Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
Worksheets("Bun Report").Range("B1").Select
Selection.NumberFormat = "m/d/yyy h:mm"
Selection.Interior.ColorIndex = 6
Selection.Interior.Pattern = xlSolid
Worksheets("Bun Report").Range("A1").Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 6
Selection.Interior.Pattern = xlSolid
Worksheets("Bun Report").Range("B4").Select
Selection.NumberFormat = "0.00"
Worksheets("Bun Report").Range("B7").Select
Selection.NumberFormat = "0.00"

End If
End Sub

Open in new window


Thanks in advance
Angie
0
Comment
Question by:Angiehncock
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
ID: 37786394
Not sure I follow, but does this work?
Sub Bun_Oven_Update_Report_Data()
'updating Bun Oven

If Not (IsEmpty(Worksheets("Bun Oven").Range("F3"))) Or _
   Not (IsEmpty(Worksheets("Bun Oven").Range("F4"))) Then
    Worksheets("Bun Oven").Range("E2:F8").Copy
    Worksheets("Bun Report").Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
    With Worksheets("Bun Report").Range("B1")
        .NumberFormat = "m/d/yyy h:mm"
        .Interior.ColorIndex = 6
        .Interior.Pattern = xlSolid
    End With
    With Worksheets("Bun Report").Range("A1")
        .Font.Bold = True
        .Interior.ColorIndex = 6
        .Interior.Pattern = xlSolid
    End With
    Worksheets("Bun Report").Range("B4").NumberFormat = "0.00"
    Worksheets("Bun Report").Range("B7").NumberFormat = "0.00"
End If

End Sub

Open in new window

0
 

Author Comment

by:Angiehncock
ID: 37792872
This work great, but I do have a question.  the formula in F3 and F4 will give me  False if the  data is untrue.   How do I write code, so that this data doesn't come through on my report?
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now