Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

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

Q to Automate Removal of Characters from Cells
 

On Saturday, July 25, 2015 at 11:10:41 AM UTC+1, Claus Busch wrote:
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.

Correct, but that was after 'manual' adjustment, I'll be adding again to the column next week. i.e. I copy/paste from a flat file, hence the " EURO etc etc

Q to Automate Removal of Characters from Cells
 

Hi,

Am Sat, 25 Jul 2015 03:18:59 -0700 (PDT) schrieb :

Correct, but that was after 'manual' adjustment, I'll be adding again to the column next week. i.e. I copy/paste from a flat file, hence the " EURO etc etc

then 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)
If InStr(rngC, "-") Then
rngC = Mid(rngC, InStr(rngC, "-"))
End If
Next
End Sub


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

Q to Automate Removal of Characters from Cells
 

Great, that seems to work perfect

One other twist, in Column E, I have similar format, except it doesn't have the - (minus sign) e.g " EURO 659.00

How could I also run the code over this column?

Q to Automate Removal of Characters from Cells
 

Hi,

Am Sat, 25 Jul 2015 04:01:53 -0700 (PDT) schrieb :

One other twist, in Column E, I have similar format, except it doesn't have the - (minus sign) e.g " EURO 659.00

How could I also run the code over this column?

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)
If InStr(rngC, "-") Then
rngC = Mid(rngC, InStr(rngC, "-"))
End If
Next
For Each rngC In Range("E2:E" & LRow)
If InStr(rngC, "EURO") Then
rngC = Mid(rngC, InStrRev(rngC, " "))
End If
Next
End Sub


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

Q to Automate Removal of Characters from Cells
 

Thank you very much, will save me a lot of manual adjustment going forward

Q to Automate Removal of Characters from Cells
 

Hi,

Am Sat, 25 Jul 2015 05:11:39 -0700 (PDT) schrieb :

Thank you very much, will save me a lot of manual adjustment going forward

you are welcome. Always glad to help.


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