• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

if then statement in vba doesn't respond with excel code

The formula in the excel sheets   cell F5=IF('Bun Mixers'!$D$9>0,IF('Bun Mixers'!$D$10>(VLOOKUP('Bun Mixers'!$D$9,'Bun Dough Recipe Information'!$A$2:$D$62,4)+(SUM(PRODUCT(VLOOKUP('Bun Mixers'!$D$9,'Bun Dough Recipe Information'!$A$2:$D$62,4),5%)))),"Actual mix time is 5% OVER setpoint",""))
 This formula works well in the excel spread sheet.  
I want to pick up this cell if it is true in a report using vba code.

the code is as follows:
Sub Bun_Mixers_Update_Data_Report()
'updating Bun Mixers Data
If Not (IsNull(Worksheets("Bun Mixers").Range("F5"))) Or Not (IsNull(Worksheets("Bun Mixers").Range("F6"))) Then

Worksheets("Bun Report").Range("E1:F8").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Worksheets("Bun Mixers").Range("E2:F9").Copy
    Worksheets("Bun Report").Range("E1").PasteSpecial xlPasteValues
    Worksheets("Bun Report").Range("E9:F16").Copy
    Worksheets("Bun Report").Range("E1:F8").PasteSpecial xlPasteFormats
    
With Worksheets("Bun Report").Range("F1")
    .NumberFormat = "m/d/yyy h:mm"
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

With Worksheets("Bun Report").Range("E1")
    .Font.Bold = True
    .Interior.ColorIndex = 6
    .Interior.Pattern = xlSolid
End With

Worksheets("Bun Report").Range("F9").HorizontalAlignment = xlCenterAcrossSelection
Worksheets("Bun Report").Range("F9").NumberFormat = "0.00"

End If

Open in new window


What will work in this code to get an either or situation?

Thanks,
Angie
0
Angiehncock
Asked:
Angiehncock
  • 2
1 Solution
 
Frank WhiteCommented:
The condition in your initial IF statement checks if the cell range is null. It will never be, as it is a range reference, and the cell also has a formula, which means it isn't 'empty' either.

Assuming the cells are using standard formatting, you could check for:
Worksheets("Bun Mixers").Range("F5").Text = "" Or Worksheets("Bun Mixers").Range("F6").Text = ""

Open in new window

0
 
AngiehncockAuthor Commented:
Unforntuately this gives me a type mismatch error '13'.  I also tried If Not(Is Empty,  this gave me everything or nothing depending on whether I used Not.
0
 
dlmilleCommented:
Since you can already do error checking in the worksheet formula such that you get a text string on the TRUE and null string on the FALSE/error, then you can test for NOT vbNullString.  I didn't rewrite your formula to do error checking.  Let's just see if this modification in the VBA works for you.

Why not just change line 4 to:

If Worksheets("Bun Mixers").Range("F5").Value <> vbNullString Or Worksheets("Bun Mixers").Range("F6").Value <> vbNullString Then

Dave
0
 
AngiehncockAuthor Commented:
This works well provided that the formula is F5 is constructed properly.   Thanks for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now