[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • 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?

  • 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

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

AngiehncockAuthor Commented:
This works well provided that the formula is F5 is constructed properly.   Thanks for your help!

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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