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
170 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

777 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