Macro question

[david45]

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.

 

[dk]

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>

 

[VoG]

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

 

[Jack in the UK]

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