VBA - Excel fomula returns a value when formula = blank.

nitrammada

Board Regular
Joined
Oct 10, 2018
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Hi Folks
I'm try to return a value to column B where if a cell in column Y (that has a formula in it) is <>0, then return "Y" on the same row in column B, else "N". Trouble is it sees the formula (=IFERROR(X8*W8,"")) as a value when in fact in some cases it is zero. I've been searching the FAQ's and from what I have read, it seems I have to Dim the range in column Y as Boolean and possibly set the range or something. Here's what I have so far but it doesn't work. I have several sheets to check and each sheet has a varying number of rows hence I'm using the row.count.end xlUp function.

VBA Code:
Sub CheckCode3()
Application.ScreenUpdating = False
 
Dim Count, i As Long
Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
'MsgBox Count
i = 7
Do While i <= Count
    If Cells(i, 25).Value <> 0 Then
        Range(Cells(i, 2), Cells(i, 2)).Value = "Y"
      
        ElseIf Cells(i, 25).Value = "" Then
        Range(Cells(i, 2), Cells(i, 2)).Value = "N"
    End If
    i = i + 1
    Loop

Application.ScreenUpdating = True
End Sub

If anyone can point me in the right direction I would be most grateful.

Kind regards
Adam
 
Last edited by a moderator:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
How about
VBA Code:
Sub CheckCode3()
Application.ScreenUpdating = False
 
Dim Count, i As Long
Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
'MsgBox Count
i = 7
Do While i <= Count
    If Cells(i, 25).Value = 0 Or Cells(i, 25).Value = "" Then
        Cells(i, 2).Value = "N"
    Else
        Cells(i, 2).Value = "Y"
    End If
    i = i + 1
Loop

Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Trouble is it sees the formula (=IFERROR(X8*W8,"")) as a value when in fact in some cases it is zero.
Strictly spoken zero is also a value. Your formula as written may evaluate to three different conditions:
- numeric equals 0
- numeric not equals 0
- string equals empty ("", or the VBA constant vbNullString)
You might consider to change your formula to:
Excel Formula:
 =IFERROR(X8*W8,0)
 
Upvote 0
How about
VBA Code:
Sub CheckCode3()
Application.ScreenUpdating = False
 
Dim Count, i As Long
Count = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
'MsgBox Count
i = 7
Do While i <= Count
    If Cells(i, 25).Value = 0 Or Cells(i, 25).Value = "" Then
        Cells(i, 2).Value = "N"
    Else
        Cells(i, 2).Value = "Y"
    End If
    i = i + 1
Loop

Application.ScreenUpdating = True
End Sub
Amazing as always Fluff, thank you so much, it works perfectly and simple too. Whilst searching the FAQ's I came across many of your solutions and have benefited greatly from them, thank you for sharing you expertise with us novices, it really is appreciated. Great work.:)
Kind regards
Adam
 
Upvote 0
Strictly spoken zero is also a value. Your formula as written may evaluate to three different conditions:
- numeric equals 0
- numeric not equals 0
- string equals empty ("", or the VBA constant vbNullString)
You might consider to change your formula to:
Excel Formula:
 =IFERROR(X8*W8,0)
Hi GWteB, thank you for your reply, I will try what you suggested as an alternative formula, thanks very much for responding, appreciate it.
Kind regards
Adam
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top