I am looking for a piece of VBA that will remove all characters/spaces to the left of the minus sign. All values are in Column D. I could have data in any Row between 2 & 500, of any value

Example is
" EURO -491.15

So I just want it to appear in cells as
-491.15

Hi,

Am Fri, 24 Jul 2015 22:37:54 -0700 (PDT) schrieb :

Example is
" EURO -491.15

So I just want it to appear in cells as
-491.15

try:

Sub Replace()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D2:D" & LRow)
rngC = Mid(rngC, InStr(rngC, "-"))
Next
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Getting a debug on this line

rngC = Mid(rngC, InStr(rngC, "-"))

"invalid procedure or call arguement"

Note that I have removed all " -EURO values already, if thats the issue, so would would to trap that error so it doesnt happen if format is OK

Hi,

Am Sat, 25 Jul 2015 02:12:29 -0700 (PDT) schrieb :

Note that I have removed all " -EURO values already, if thats the issue, so would would to trap that error so it doesnt happen if format is OK

please post some of your strings.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Saturday, July 25, 2015 at 10:28:03 AM UTC+1, Claus Busch wrote:
Hi,

Am Sat, 25 Jul 2015 02:12:29 -0700 (PDT) schrieb :

Note that I have removed all " -EURO values already, if thats the issue, so would would to trap that error so it doesnt happen if format is OK

please post some of your strings.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional


-491.15

-742.00

Details above, not row 2 is blank

Hi,

Am Sat, 25 Jul 2015 02:57:12 -0700 (PDT) schrieb :

-491.15

-742.00

but that is your expected output. You don't need a macro.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional