Simple bit of code needed

[MOB]

In column T there are numbers with an R at the beginning eg R 70.00

The number of spaces between the R and the number may vary

I need a bit of code that will replace all of the spaces and R's with nothing, and if it did contain an R the number needs to be turned negative.

Thanks in advance!

 

[Richard Schollar]

Hi
Try this (enter each line in the Immediate Window (Ctrl+G) of the VBE and press Enter after each one:

set r = intersect(activesheet.usedrange,range("T:T"))
r.value = evaluate("if(row(),if(left(" & r.address & ")=""R"",-VALUE(MID(" & r.address & ",2,255))," & r.address & "))")

 

[MOB]

That works nicely thanks

 

[MOB]

Oops - the R is actually to the right not the left ie 70 R

I had a go at changing left to right in the code, but I think the mid bit is not working properly now.

?

 

[Peter_SSs]

Oops - the R is actually to the right not the left ie 70 R

I had a go at changing left to right in the code, but I think the mid bit is not working properly now.

?

Try this (assumes data starts in T1)

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Convert_R()<br>    <SPAN style="color:#00007F">With</SPAN> Columns("T")<br>        .Replace What:="R", Replacement:="-", LookAt:=xlPart, MatchCase:=False, _<br>            SearchFormat:=False, ReplaceFormat:=False<br>        .TextToColumns Destination:=Range("T1"), DataType:=xlDelimited, Tab:=False, _<br>            Semicolon:=False, Comma:=False, Space:=False, Other:=False, _<br>            FieldInfo:=Array(1, 1), TrailingMinusNumbers:=<SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If you don't really need code, you can do the same steps manually fairly quickly:

1. Find and replace; Find "R", replace with "-"
2. Text to columns with no delimiters and Trailing minus ticked in the 'Advanced' section of step 3.

 

[Richard Schollar]

Change the r.Value bit to this:

r.value = evaluate("if(row(),if(right(" & r.address & ")=""R"",-VALUE(LEFT(" & r.address & ",LEN(" & r.address & ")-1))," & r.address & "))")

 

[MOB]

Thanks guys!