InStr (1, cell.Value, "-") does not seem to work with Not

Question

I have a condition that doesn't seem to work.

If Not InStr(1, cell.Value, "-") Then 'Do Something Else 'Do something else End If 

Where cell.Value is either the numbers in the spreadsheet with a dash: "6621-123", or without a dash: "555321"

The first If allows you to ignore both Else . Any ideas why this is not working?

Answer 1

InStr returns 0 without matching (not -1, because VBA row indices are based on 1) and not 0 is true ( -1 ); so that all other possible values> 0 can be returned.

 If InStr(1, cell.Value, "-") = 0 Then '// not present Else '// present 

Answer 2

First, ignoring both end-to-end and Else.

I would think that if one of the actual properties of Range.Value contained a hyphen (aka Chr(45 ), and the other does not, then the behavior will not be the same for both of your samples, regardless of whether you got the InStr function syntax correctly . you have enough to define a True / False condition, although I usually use the CBool wrapper around Instr to remind myself that its result is used in a Boolean way.

I believe that your “value” representing a hyphen is actually 6621123 not 6621-123 and that the formatting of the number was applied to the cell (for example, the user number format is 0000-000 . In this case, if you want to determine if a hyphen exists in the displayed value , use Range.Text property.

 If CBool(InStr(1, cell.Text, "-")) Then 'hyphen exists as seen on worksheet either in .Value or .Text Else 'hyphen does not exist as seen on worksheet End If 

---

¹ _{In VBA, False is zero, and for all goals and targets, True is what is not False. Strictly speaking, True in VBA is mathematically treated as (-1), while a TRUE sheet is resolved as 1.}