Link to home
Create AccountLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Looking at negative numbers and capturing

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 User generated imageDim 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
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

HI,

What do you mean by "capturing" ???
Also, do not confuse cells format with cells value, these arn't related to each other.
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.
Avatar of Fordraiders

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
would Cint    or   Cdec using in vba routine work...just converting it code purposes ?
cint will result in a loss of data if your Numbers arn't integer.
and cdec is useless for your case.