<div>
Dim CellDataEvaluate As Currency
</div>

<div>
martin , the value showing in the cell is &nbsp;-0.82% &nbsp; the text value is -0.82.... 
my fear is the text then may be &nbsp; at some point . &nbsp; &nbsp; &nbsp; .-0.82% &nbsp; ?? 
 
Thanks 
fordraiders
</div>

<div>
well, i guess i could strip the % sign anyway , just in the code temporarily ?
</div>

<div>
Test this little macro. 

<pre><code class="code-10 nolinks" id="code-20-42771657-1">Sub test()

Dim CellDataEvaluate As Currency
' for example
CellDataEvaluate = -0.82
 

If CellDataEvaluate &lt; 0 Then
 MsgBox &quot;Negative&quot;
 MsgBox CellDataEvaluate + 5
End If
End Sub
</code></pre>
</div>

<div>
The problem with your code is the 
<pre><code class="code-1 nolinks" id="code-20-42771706-1">IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, &quot;.&quot;) And InStr(1, CellDataEvaluate, &quot;-&quot;) &lt; 0
</code></pre>
 Will never return anything smaller than 0 (zero), Isnumeric returns true or false, Instr returns the position of the character if found, IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, &quot;.&quot;) is like &quot;if true and zero&quot; which will always be ZERO, then you add another 
And InStr(1, CellDataEvaluate, &quot;-&quot;) &lt;&nbsp;0 &nbsp; this &lt;0 only applies to the last INSTR not to both of them and also will never be anything smaller than ZERO. 
 
After you confirm that it IS numeric then you 
only need to check for CellDataEvaluate&lt;0 
no need for the sign checking 

<pre><code class="code-10 nolinks" id="code-20-42771706-2">Dim CellDataEvaluate As String
' for example
CellDataEvaluate = -0.82
 


' catch a negative number
 If IsNumeric(CellDataEvaluate) Then
 if CDec(CellDataEvaluate)&lt; 0 Then ' this should catch a negative string
 Do Stuff
end if
end if
</code></pre>
</div>

<div>
If the cells contain +ve and -ve percentage values, only cell with 100% will contain the actual value 1 underneath the cell else all the percentage values whether +ve or -ve will be less than 0. So you cannot check them for &lt;0. 
Instead read the Text of the cell like below... 
 
Assuming your percentage value -0.82% is in A2, then following line will detect if it was -ve percentage value. 
 

<pre><code class="code-1 nolinks" id="code-20-42771745-1">If IsNumeric(Range(&quot;A2&quot;).Value) And InStr(Range(&quot;A2&quot;).Text, &quot;-&quot;) &gt; 0 Then
 MsgBox &quot;Negative&quot;
End If
</code></pre>
See if you can incorporate this into your code.
</div>

Data in spreadsheet awaiting call to "IsNegative"

Data_For_Call_To_IsNegative.jpg

Workbook (macroEnabled) containing macro "Main" which calls the function "IsNegative" (useable in either VB6 or in VBA withing Excel, Access,Word etc)

TestForNegativeValueInExcel.xlsm

Data Engineer

Fordraiders

<div>
Thanks all, Testing suggestions 
 
 
Martin, Just trying your bit: 
Not Working when evaluating this code before the If Then. 
&nbsp; lastColumn = 11 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; While Len(.Cells(lastRow, 1)) &gt;&nbsp;0 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; lastRow = lastRow + 1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Wend 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; lastRow = lastRow - 1 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; For i = 2 To lastRow 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; For j = 1 To lastColumn 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellDataEvaluate = Trim(.Cells(i, j).Value) &nbsp; &nbsp;&lt;--- &nbsp; &nbsp;error here type mismatch 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellDataEvaluate = Replace(CellDataEvaluate, &quot;%&quot;, &quot;&quot;) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellDataEvaluate = Replace(CellDataEvaluate, &quot;$&quot;, &quot;&quot;) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellDataEvaluate = Replace(CellDataEvaluate, &quot;#&quot;, &quot;&quot;) 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CellDataEvaluate = Trim(CellDataEvaluate)
</div>

<div>
I don't believe you need any of the last five rows because when CellDataEvaluate is defined as currency it's all done for you. Try it and let me know if something doesn't work after those lines are removed.
</div>

<div>
just a side note. 
 
Is there a way to actually check the &nbsp;cell formatting for a given cell ? 
General, Text, percentage, &nbsp;custom &nbsp; ? 
 
&nbsp;Trim(.Cells(i, j).Value) &nbsp; &nbsp;for this &nbsp; cell range ? 
 
Thanks
</div>

<div>
Chris, Just to clarify. 
Your code is actually telling me that &nbsp;regardless of cell formatting, what the actual cell value is ? a true number or not ? 
varVal &nbsp;is the variant being tested ? 
 
The reason i ask is because if a cell happens to contain an alpha character, i need to handle it.. 
&nbsp; &nbsp; &nbsp;End If 
&nbsp; &nbsp; &nbsp; Else 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nVal = CLng(varVal) ' &nbsp;&lt; &nbsp; ------------ &nbsp; ERROR HERE IF THE CELLS CONTAINS alpha characters ? 
&nbsp; &nbsp; &nbsp; End If 
&nbsp; &nbsp; &nbsp; isNegative = (nVal &lt;&nbsp;0) 
&nbsp; &nbsp; Case Else 
&nbsp; &nbsp; &nbsp; MsgBox &quot;Unknown value passed to IsNegative() fucntion&quot;
</div>

<div>
Yes, it will test for any type of value passed to it. A variant can hold any variable type. Remember, this code can be used in VB6 or VBA not just within a spreadsheet and so other variable types could be used in VBA apps. 
 
With Excel of course the values are usually either a number or a string. I do not think that the formatting of the cell actually makes any difference, but I had to incorporate every possibility in order to make this a useful function for not just Excel. 
 
Cheers 
Chris
</div>

<div>
Chris, Thanks for the response , but what about the alpha character error. and how to handle ? 
fordraiders
</div>

<div>
OK, I missed the possibility of &quot;Nothing&quot; being passed (an empty cell) and also a cell that has alpha characters, so I tweaked the code to handle that, plus I have given the user the chance to place whatever values they like in any Cell in Range (&quot;E1&quot;) through to Range(&quot;E10&quot;) 
before running the macro. (For testing purposes). 
 
Give that a try. 
 
If all values process without error, then the function &quot;IsNegative&quot; in the code is the one to use. 
 
The revised Workbook is attached and the code is now: 
 

<pre><code class="code-10 nolinks" id="code-20-42773197-1">Option Explicit

'(This Function can be used in VBA and VB6. It returns an indication (True or False) whether a value passed to it (of any type)
'is positive or negative. It logically only applies to numbers or currency values, all other types return Positive (i.e. &quot;Not Negative&quot;) 

Sub Main()
 Dim CellDataEvaluate As String
 Dim strAddress As String
 Dim x As Integer
 Range(&quot;A1&quot;).Select
 CellDataEvaluate = &quot;-0.82&quot;
 If MsgBox(&quot;Place various values in column &quot; &amp; Chr(34) &amp; &quot;E&quot; &amp; Chr(34) &amp; &quot; in rows 1 to 10&quot;, vbDefaultButton1 Or vbOKCancel) = vbCancel Then
 Exit Sub
 End If
 MsgBox (&quot;Code has assigned a string value to variable 'CellDataEvaluate' of -0.82&quot; &amp; vbCrLf &amp; vbCrLf &amp; _
 &quot;The value for CellDataEvaluate is &quot; &amp; IIf(isNegative(CellDataEvaluate), &quot;Negative&quot;, &quot;Positive&quot;))
 For x = 1 To 10
 strAddress = &quot;E&quot; &amp; CStr(x)
 Range(strAddress).Select
 If MsgBox(&quot;The value in cell &quot; &amp; strAddress &amp; &quot; is &quot; &amp; IIf(isNegative(Range(strAddress)), &quot;Negative&quot;, &quot;Positive&quot;), vbDefaultButton1 Or vbOKCancel) = vbCancel Then
 Range(&quot;A1&quot;).Select
 Exit For
 End If
 Next
 MsgBox &quot;Evaluation complete&quot;
End Sub

Public Function isNegative(varVal As Variant) As Boolean
 'Note: This code will fail the test on a numeric expressed in hex format (will always return false)
 ' so do not use it as a test on a Hex value.
 Dim nVarType As Integer
 Dim nVal As Long
 nVarType = VarType(varVal)
 Select Case nVarType
 Case vbNull, vbDate, vbObject, vbError, vbVariant, vbDataObject, vbUserDefinedType, vbArray
 isNegative = False
 Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbDecimal, vbByte, vbVariant
 isNegative = varVal &lt; 0
 Case 0 'a blank cell
 isNegative = False
 Case vbString
 Dim strCurr As String
 strCurr = &quot;$&quot; 'could test for other currency symbols by adding extra currency symbols to this string
 If InStr(varVal, &quot;.&quot;) &gt; 0 Or InStr(strCurr, Left(Trim(varVal), 1)) &gt; 0 Or InStr(strCurr, Right(Trim(varVal), 1)) &gt; 0 Then
 If InStr(strCurr, Left(Trim(varVal), 1)) &gt; 0 Then
 nVal = CCur(varVal) 'Remember,even a currency value can be negative!
 Else
 varVal = Replace(varVal, Chr(34), &quot;&quot;) 'strip out surrounding strings
 If IsNumeric(varVal) Then
 nVal = Val(varVal)
 Else
 nVal = 1 'Forcing a false into &quot;IsNegative&quot; since this &quot;mixed&quot; value of characters and numbers does not represent a true number
 End If
 End If
 Else
 If IsNumeric(varVal) Then
 nVal = CLng(varVal)
 End If
 End If
 isNegative = (nVal &lt; 0)
 Case Else
 MsgBox &quot;Unknown value passed to IsNegative() function&quot;
 End Select
End Function
</code></pre>
<a href="https://filedb.experts-exchange.com/incoming/2019/01_w02/1409365/TestForNegativeValueInExcel.xlsm" target="_blank" class="file-inline" title="TestForNegativeValueInExcel.xlsm (19 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>TestForNegativeValueInExcel.xlsm</a>
</div>

<div>
So who has been awarded points for helping?
</div>

<div>
<div class="content wysiwyg-content">
excel 2010 vba 
 
The data is being grabbed off a worksheet 
 
Trying to catch a negative number and do stuff with it.. 
 
Dim CellDataEvaluate As String 
' for example 
CellDataEvaluate = &nbsp;-0.82 
&nbsp; 
 
 
' catch a negative number 
&nbsp;If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, &quot;.&quot;) And InStr(1, CellDataEvaluate, &quot;-&quot;) &lt;&nbsp;0 Then &nbsp; ' this should catch a negative string 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;CellDataEvaluate = CDec(CellDataEvaluate) 
 
The line above is not catching a negative number ? 
 
 
Thanks 
fordraiders
</div>
</div>

excel 2010 vba

The data is being grabbed off a worksheet

Trying to catch a negative number and do stuff with it..

Dim CellDataEvaluate As String
' for example
CellDataEvaluate = -0.82
 


' catch a negative number
 If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") And InStr(1, CellDataEvaluate, "-") < 0 Then ' this should catch a negative string
 CellDataEvaluate = CDec(CellDataEvaluate)

The line above is not catching a negative number ?


Thanks
fordraiders

evaluate a negative number

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.