Check for character in string

[dksaluki]

I have a range of cells with a name in them. What is the best way to
check to see if each cell has the "_" character or the "-" character
in the string? I would also like to remove it. Right now i have:

If Not Range("A4").Find("-") Is Nothing Then
fileName = VBA.Replace(Range("A4").Value, "-", "")
ElseIf Not Range("A4").Find("_") Is Nothing Then
fileName = VBA.Replace(Range("A4").Value, "_", "")
End If

I'm using the VBA.Replace method to replace the above characters with
a blank. (If the .Find method is not nothing, replace the characters)
It works fine, just didn't know if there was a faster, more efficient
way? ....like an array or something...

dck

 

[Dave Peterson]

You could use VBA's InStr() function to look for a string within a string.

But why bother? Why not just do the replace:

fileName = Replace(Range("A4").Value, "-", "")
fileName = Replace(Range("A4").Value, "_", "")

Is there a reason you used VBA.Replace instead of Replace? Why the
qualification?

 

[Rick Rothstein \(MVP - VB\)]

Given the If-Then-ElseIf structure the OP used in his message, shouldn't the
second statement use the fileName (as set by the first statement) as the
argument to its Replace function?

fileName = Replace(Range("A4").Value, "-", "")
fileName = Replace(fileName, "_", "")

or, I guess they could be combined into a single statement...

fileName = Replace(Replace(Range("A4").Value, "-", ""), "_", "")

Rick

 

[Dave Peterson]

I agree.

Thanks for the addendum.

Given the If-Then-ElseIf structure the OP used in his message, shouldn't the
second statement use the fileName (as set by the first statement) as the
argument to its Replace function?

fileName = Replace(Range("A4").Value, "-", "")
fileName = Replace(fileName, "_", "")

or, I guess they could be combined into a single statement...

fileName = Replace(Replace(Range("A4").Value, "-", ""), "_", "")

Rick