?
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
Medium Priority
?
179 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
[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
2 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

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