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

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?

dksaluki wrote:

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

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" wrote in message
...
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?

dksaluki wrote:

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

I agree.

Thanks for the addendum.

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
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?

dksaluki wrote:

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

--

Dave Peterson