Looking at negative numbers and capturing

Fordraiders
Fordraiders used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Fabrice LambertConsulting
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

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Consulting
Distinguished Expert 2017
Commented:
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

Author

Commented:
would Cint    or   Cdec using in vba routine work...just converting it code purposes ?
Fabrice LambertConsulting
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial