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

Dim 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

VBAMicrosoft ExcelMicrosoft Office

Last Comment

Fabrice Lambert

8/22/2022 - Mon

Fabrice Lambert

10/2/2018

HI,

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

Roy Cox

10/2/2018

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.

Fordraiders

10/2/2018

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

Your help has saved me hundreds of hours of internet surfing.

fblack61

ASKER CERTIFIED SOLUTION

Fabrice Lambert

10/2/2018

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts