What regular expression I can use in vba to Convert the number 123456- to -123456. I am asking this because I am trying to read txt file and need to recognize when number is negative and positive.
Any ideas?
What regular expression I can use in vba to Convert the number 123456- to -123456. I am asking this because I am trying to read txt file and need to recognize when number is negative and positive.
Any ideas?
Why RegEx?
[vba]
Selection.Replace What:="-", Replacement:=""
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
This code does not do what I need. It just takes the "-" out and I need to put it in front of the number so excel can recognize it as a negative number
Data would contain the number "123456-"
This will not work of course if there is more informationin the variable than the number.
[VBA]If InStr(1, Data, "-") > 0 And InStr(1, Data, "-") <> 1 Then Data = "-" & Replace(Data, "-")[/VBA]
Here is a VBA solution
[vba]
Dim rng As Range
Dim cell As Range
Dim LastRow As Long
Dim i As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
.Rows(1).Insert
.Range("M1").Value = "tmp"
Set rng = .Range("M1").Resize(LastRow + 1)
rng.AutoFilter field:=1, Criteria1:="*-"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Nothing Is Nothing Then
rng.AutoFilter
rng.Replace "-", ""
.Range("M1").Value = 0
For Each cell In rng
cell.Value = cell.Value * -1
Next cell
End If
.Rows(1).Delete
End With
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber