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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 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