We help IT Professionals succeed at work.

Looking at negative numbers and capturing

80 Views
Last Modified: 2018-10-15
Excel 2010  vba

windows 10

What I have:
worksheet with negative numbers in cells  like    -.29  or -0.2900 or -00.29   etc...

I have a variable(CellDataEvaluate) that needs negative number evaluateDim CellDataEvaluate As String


formatting because the value is getting posted to a text file.

However, I'm not properly capturing the negative numbers with my code.

If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") < 0 Then
                    CellDataEvaluate = Format(CellDataEvaluate, "-0.0000")


If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") < 0 Then
                    CellDataEvaluate = Format(CellDataEvaluate, "-0.0000")
                    ' number is not negative gp
                    If IsNumeric(CellDataEvaluate) And InStr(1, CellDataEvaluate, ".") > 0 Then
                        CellDataEvaluate = Format(CellDataEvaluate, "0.0000")
                        
                        If IsNumeric(CellDataEvaluate) And CellDataEvaluate > "0.0000" And CellDataEvaluate < "1.000" Then

                          CellDataEvaluate = Format(CellDataEvaluate, "0.0000")      '* 100
                         
                        Else
                           CellDataEvaluate = CellDataEvaluate
                        End If ' inside else if
                    
                    
                    
                    Else
                                        
                         
                    
                    End If
                    
                    
                    
            End If ' evaluates for Negative number
          
         End If ' evaluate cells if

Open in new window




Thanks
fordraiders
Comment
Watch Question

Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
HI,

What do you mean by "capturing" ???
Also, do not confuse cells format with cells value, these arn't related to each other.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Are you having problems saving the numbers to a csv file?

Are the cells actually formatted as numbers and not text?

To test if a number is negative you simply need to test if the value is less than zero.

Author

Commented:
Folks , I have 2 things going on here:
#1 The  "Dim CellDataEvaluate As String"
the cell value is what I'm grabbing  

lastColumn = 12
            While Len(.Cells(lastRow, 1)) > 0
                lastRow = lastRow + 1
            Wend
            lastRow = lastRow - 1
            For i = 2 To lastRow
                For j = 1 To lastColumn
                    CellDataEvaluate = Trim(.Cells(i, j).Value)
                    CellDataEvaluate = Replace(CellDataEvaluate, "%", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "$", "")
                    CellDataEvaluate = Replace(CellDataEvaluate, "#", "")
                    CellDataEvaluate = Trim(CellDataEvaluate)
                    ' check for instring  evaluation also
                    
                   ' CellDataOrig = Format(CellDataOrig, "0.0000")
                    CellDataEvaluate = CellDataEvaluate

Open in new window



Are you having problems saving the numbers to a csv file?
No..
Are the cells actually formatted as numbers and not text?
Formatted as "custom"
To test if a number is negative you simply need to test if the value is less than zero.

but my code is not correct...



Thanks
fordraiders
Consulting
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
would Cint    or   Cdec using in vba routine work...just converting it code purposes ?
Fabrice LambertConsulting
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
cint will result in a loss of data if your Numbers arn't integer.
and cdec is useless for your case.