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 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
LVL 3
FordraidersAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
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 ManagerCommented:
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.
FordraidersAuthor 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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Fabrice LambertConsultingCommented:
Also, if you compare numbers with string, you won't get expected result, so leave those numbers as numeric values, the format is only there for display.

Sample code:
    '// Assign 0.5 to cell A1
range("A1") = 0.5

    '// Display the cell as 0.500, but the value is still 0.5
range("A1").NumberFormat = "0.000"

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
would Cint    or   Cdec using in vba routine work...just converting it code purposes ?
Fabrice LambertConsultingCommented:
cint will result in a loss of data if your Numbers arn't integer.
and cdec is useless for your case.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.