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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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