VBA code to check if cell's value is 0 not working.

Question

VBA code to check if cell's value is 0 not working.

Anonymous

Hi all, I'm new to VBA and am currently stuck at a step.

I basically have an excel sheet that looks like a daily record report for employees to key in what equipment they haxe taken out of the depot, and how they have used it for. I have a cell that calculates the time difference between manhours for when a equipment is out of the depot and the number of manhours that the equipment was used for, and this value should essentially be 0

Example:

Manhours equipment is out of depot: 16 hours (cell A5)

Manhours (input by user) equipment was used for: 16 hours (cell B5)

Time difference: 0 (cell C5; =A5 - B5)

What I want to do in for a message box to appear when the value is not 0, to remind the user to check the manhours they have input. It looks like this:

If Range("C5").value <> 0 Then

Cancel = True

Msgbox "The number of manhours for the equipment do not tally. Please check again."

The code works when the time difference is not 0, but when the time difference is 0, the message box still appears.

Where am I going wrong with this?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

5 additional answers

Answer 1

Rory Archibald 18,885 Volunteer Moderator

Formatting does not round the value, it just changes the display. You could amend the formula to =ROUND(A5-B5,2)

0 comments

Answer 2

Anonymous

I feel silly that the solution was so simple!

Thanks a lot Rory!

0 comments

Answer 3

Anonymous

Set the properties of A5, B5, and C5 as 'Custom' --> [h]:mm

Then (in my Excel) the messagebox functions as you wish.

0 comments

Answer 4

Hi Rory,

Thank you for your reply!

This line of code is part of a longer piece of code, and the worksheet has been referenced to the active sheet.

You've made a very good point. There are indeed decimal numbers because I'm dealing with time, how would I apply rounding using code or formula? At the moment, I have formatted the cells to be numbers with up to 2 decimal points. I'm assuming that's not enough?

Answer 5

Hi,

A couple of things to check:

You aren't specifying a worksheet for the Range. It's usually safer to be explicit about that.
Are you sure the value is actually 0 and not a small decimal? If you are actually dealing with time values (which are decimals) rather than an actual number of hours (just 16) you often get small floating point errors, If that's the case, you should probably apply rounding in the code, or in the formula.

Share via

VBA code to check if cell's value is 0 not working.

5 additional answers