VBA UDF Remove * [star{s}] ___SOLVED By PaddyD

[Jack in the UK]

I have designed this UDF OK wonderful for races or { [ } } ( ) or whatever but i can not remover stars * ??? why not can this be edited??? Any ideas guys

Function JackintheUK_RemvBraces(MyRange As Range) As String
Dim strResult As String
Application.Volatile
If InStr(1, MyRange, "(")<> 0 Then
strResult = Trim(Left(MyRange, _
WorksheetFunction.Search("(", MyRange) - 1))
Else
strResult = MyRange
End If

JackintheUK_RemvBraces = strResult
End Function

_________________<embed width="200" height="50" src="http://homepage.a5.com/~kristy/misc/jack.swf">
This message was edited by Jack in the UK on 2002-09-18 15:27

 

[PaddyD]

Wierd, knowing sod all about the inticacies of VBA, I

a) assumed that the same method you'd use in a workbook find - replace for special characters would apply
b) was surprised Jack hadn't thought of it!

But:

<pre>


Function JackintheUK_RemvStars(MyRange As Range) As String
Dim strResult As String
Application.Volatile
If InStr(1, MyRange, "~*") <> 0 Then
strResult = Trim(Left(MyRange, _
WorksheetFunction.Search("~*", MyRange) - 1))
Else
strResult = MyRange
End If

JackintheUK_RemvStars = strResult
End Function



</pre>


Has no effect whatsoever. Bugger!

Paddy

 

[Jack in the UK]

PaddyD

LOLOLOLOLOLOLOLO OUT VERY LOUD !!!

Duh i dun no!

HELP !!!! i try all i could tide* ** *** ?? ?* and ermmm

Yup i did all i could think off!!!

 

[PaddyD]

found one that works:<pre>

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</pre>


see:

http://makeashorterlink.com/?S146526D1


Paddy
This message was edited by PaddyD on 2002-09-18 15:13

 

[Jack in the UK]

Hi PaddyD

Ok some errors leave trail of _____ underscore so i have edited

STRING, i, 1), "")

PERFECTION!!! Well done tanks for that .. I love UDF

Your friend Jack!