# Macro question

#### david45

##### New Member
Question
I receive a report that has # sign after a number which is suppose to be a negative.

I recorder a macro to replace # sign with a negative sign. But the only problem is the # sign comes after the number example 45.23#. After the macro it look like 45.23- is there any way I can get the macro to place the negative in front of the number.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
What's your current code? You can use the CDbl or CCur conversion function to change 100.00- to -100.00 e.g.

<pre>
Sub Test()
Dim rngeCell As Range

Selection.Replace "#", "-"

For Each rngeCell In Selection.Cells
rngeCell = CDbl(rngeCell)
Next rngeCell

End Sub
</pre>

This function will do what you are after

Function rplc(vl As Variant)
If Right(vl, 1)<> "#" Then
rplc = vl
Else
rplc = "-" & Left(vl, Len(vl) - 1)
End If
End Function

e.g. if A1 contains 45.33# then the function will return -45.33. If there is no # it just copies the number.

So with your "weird" numbers in column A, in Column B enter

=RPLC(A1)

and there you go, by copying the formula down.

Obviously you can turn this into a Sub and loop through all your data.

[Edit = correct spelling and last paragraph added]
This message was edited by VoG™ on 2002-09-20 16:39

Public Function CleanFileName(fNameStr As String)
Dim i As Integer
Const NO_NO_STRING = "#" 'Add or remove "no-no's"
For i = 1 To Len(NO_NO_STRING)
fNameStr = Application.WorksheetFunction.Substitute(fNameStr, _
Mid(NO_NO_STRING, i, 1), "-")
Next i
CleanFileName = fNameStr
End Function

Replies
0
Views
72
Replies
3
Views
223
Replies
2
Views
204
Replies
6
Views
123
Replies
1
Views
134

1,202,911
Messages
6,052,517
Members
444,588
Latest member
ViJN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back