I need to define a value for rangename. I have this working so far:
rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" & i).Value
Debug.Print rangename
Unfortunately, the range name has SPACES in it and when I use it to create a
named range, it GACKS. How do I remove the spaces from this?
Thanks,
Barb Reinhardt
look at the trim function it may help
"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
>I need to define a value for rangename. I have this working so far:
>
> rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> i).Value
> Debug.Print rangename
>
> Unfortunately, the range name has SPACES in it and when I use it to create
> a
> named range, it GACKS. How do I remove the spaces from this?
>
> Thanks,
> Barb Reinhardt
Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am not
sure how to add it to what I have. Suggestions?
Thanks,
Barb Reinhardt
"OZDOC" wrote:
> look at the trim function it may help
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> >I need to define a value for rangename. I have this working so far:
> >
> > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> > i).Value
> > Debug.Print rangename
> >
> > Unfortunately, the range name has SPACES in it and when I use it to create
> > a
> > named range, it GACKS. How do I remove the spaces from this?
> >
> > Thanks,
> > Barb Reinhardt
>
>
>
Range("a" & i).Value
I am guessing this is where the name comes from so this reference cell can
youshow me what you have in it ? formula ?
"Barb Reinhardt" <[email protected]> wrote in message
news:[email protected]...
> Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am
> not
> sure how to add it to what I have. Suggestions?
>
> Thanks,
> Barb Reinhardt
>
> "OZDOC" wrote:
>
>> look at the trim function it may help
>>
>> "Barb Reinhardt" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >I need to define a value for rangename. I have this working so far:
>> >
>> > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
>> > i).Value
>> > Debug.Print rangename
>> >
>> > Unfortunately, the range name has SPACES in it and when I use it to
>> > create
>> > a
>> > named range, it GACKS. How do I remove the spaces from this?
>> >
>> > Thanks,
>> > Barb Reinhardt
>>
>>
>>
Hello -
You can use the following ...
rangename = Instr(rangename, " ", "")
Joe
Barb Reinhardt wrote:
> Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am not
> sure how to add it to what I have. Suggestions?
>
> Thanks,
> Barb Reinhardt
>
> "OZDOC" wrote:
>
> > look at the trim function it may help
> >
> > "Barb Reinhardt" <[email protected]> wrote in message
> > news:[email protected]...
> > >I need to define a value for rangename. I have this working so far:
> > >
> > > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> > > i).Value
> > > Debug.Print rangename
> > >
> > > Unfortunately, the range name has SPACES in it and when I use it to create
> > > a
> > > named range, it GACKS. How do I remove the spaces from this?
> > >
> > > Thanks,
> > > Barb Reinhardt
> >
> >
> >
Darn ... I must be stupid ... here we go ...
rangename = Replace(rangename, " ", "")
Sorry about that ...
Joe
Joe HM wrote:
> Hello -
>
> You can use the following ...
> rangename = Instr(rangename, " ", "")
>
> Joe
>
>
>
> Barb Reinhardt wrote:
> > Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am not
> > sure how to add it to what I have. Suggestions?
> >
> > Thanks,
> > Barb Reinhardt
> >
> > "OZDOC" wrote:
> >
> > > look at the trim function it may help
> > >
> > > "Barb Reinhardt" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I need to define a value for rangename. I have this working so far:
> > > >
> > > > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> > > > i).Value
> > > > Debug.Print rangename
> > > >
> > > > Unfortunately, the range name has SPACES in it and when I use it to create
> > > > a
> > > > named range, it GACKS. How do I remove the spaces from this?
> > > >
> > > > Thanks,
> > > > Barb Reinhardt
> > >
> > >
> > >
Perhaps this will help... Note Application.Trim and Trim are different. You
probably want application.substitute
Sub TrimTest()
Dim str As String
str = " This Is Only A Test "
MsgBox Trim(str)
MsgBox Application.Trim(str)
MsgBox Application.Substitute(str, " ", "")
End Sub
--
HTH...
Jim Thomlinson
"Barb Reinhardt" wrote:
> Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am not
> sure how to add it to what I have. Suggestions?
>
> Thanks,
> Barb Reinhardt
>
> "OZDOC" wrote:
>
> > look at the trim function it may help
> >
> > "Barb Reinhardt" <[email protected]> wrote in message
> > news:[email protected]...
> > >I need to define a value for rangename. I have this working so far:
> > >
> > > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> > > i).Value
> > > Debug.Print rangename
> > >
> > > Unfortunately, the range name has SPACES in it and when I use it to create
> > > a
> > > named range, it GACKS. How do I remove the spaces from this?
> > >
> > > Thanks,
> > > Barb Reinhardt
> >
> >
> >
The value in this cell is something like "Company Name" and I need to change
it either to "CompanyName" or "Company_Name". It's printing what's there,
but range names can't have spaces and I need to remove them. I'd like to
know how I use the SUBSTITUTE function with this
rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" & i).Value
So that "Company Name" can be converted to "Company_Name".
"OZDOC" wrote:
> Range("a" & i).Value
>
> I am guessing this is where the name comes from so this reference cell can
> youshow me what you have in it ? formula ?
>
>
>
> "Barb Reinhardt" <[email protected]> wrote in message
> news:[email protected]...
> > Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am
> > not
> > sure how to add it to what I have. Suggestions?
> >
> > Thanks,
> > Barb Reinhardt
> >
> > "OZDOC" wrote:
> >
> >> look at the trim function it may help
> >>
> >> "Barb Reinhardt" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >I need to define a value for rangename. I have this working so far:
> >> >
> >> > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> >> > i).Value
> >> > Debug.Print rangename
> >> >
> >> > Unfortunately, the range name has SPACES in it and when I use it to
> >> > create
> >> > a
> >> > named range, it GACKS. How do I remove the spaces from this?
> >> >
> >> > Thanks,
> >> > Barb Reinhardt
> >>
> >>
> >>
>
>
>
Here we go ...
rangename = Replace(Workbooks(curbook).Worksheets(datasheet).Range("a"
& i).Value, " ", "_")
Joe
Barb Reinhardt wrote:
> The value in this cell is something like "Company Name" and I need to change
> it either to "CompanyName" or "Company_Name". It's printing what's there,
> but range names can't have spaces and I need to remove them. I'd like to
> know how I use the SUBSTITUTE function with this
>
> rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" & i).Value
>
> So that "Company Name" can be converted to "Company_Name".
>
> "OZDOC" wrote:
>
> > Range("a" & i).Value
> >
> > I am guessing this is where the name comes from so this reference cell can
> > youshow me what you have in it ? formula ?
> >
> >
> >
> > "Barb Reinhardt" <[email protected]> wrote in message
> > news:[email protected]...
> > > Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am
> > > not
> > > sure how to add it to what I have. Suggestions?
> > >
> > > Thanks,
> > > Barb Reinhardt
> > >
> > > "OZDOC" wrote:
> > >
> > >> look at the trim function it may help
> > >>
> > >> "Barb Reinhardt" <[email protected]> wrote in
> > >> message
> > >> news:[email protected]...
> > >> >I need to define a value for rangename. I have this working so far:
> > >> >
> > >> > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
> > >> > i).Value
> > >> > Debug.Print rangename
> > >> >
> > >> > Unfortunately, the range name has SPACES in it and when I use it to
> > >> > create
> > >> > a
> > >> > named range, it GACKS. How do I remove the spaces from this?
> > >> >
> > >> > Thanks,
> > >> > Barb Reinhardt
> > >>
> > >>
> > >>
> >
> >
> >
Jim, the reason wanted to see the formula in the cell is it may be possible
to re write it to fix the problem,
i.e. you could if it is looking up a reference trim from that ref point, you
could use find to find the space then use left function right function etc,
but it is hard to sort something without this, in other words fix it before
the macro not in the macro ? don't know if it was you want but just an idea
"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Perhaps this will help... Note Application.Trim and Trim are different.
> You
> probably want application.substitute
>
> Sub TrimTest()
> Dim str As String
>
> str = " This Is Only A Test "
> MsgBox Trim(str)
> MsgBox Application.Trim(str)
> MsgBox Application.Substitute(str, " ", "")
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Barb Reinhardt" wrote:
>
>> Trim leaves ONE space. I'd like to use the SUBSTITUTE function, but am
>> not
>> sure how to add it to what I have. Suggestions?
>>
>> Thanks,
>> Barb Reinhardt
>>
>> "OZDOC" wrote:
>>
>> > look at the trim function it may help
>> >
>> > "Barb Reinhardt" <[email protected]> wrote in
>> > message
>> > news:[email protected]...
>> > >I need to define a value for rangename. I have this working so far:
>> > >
>> > > rangename = Workbooks(curbook).Worksheets(datasheet).Range("a" &
>> > > i).Value
>> > > Debug.Print rangename
>> > >
>> > > Unfortunately, the range name has SPACES in it and when I use it to
>> > > create
>> > > a
>> > > named range, it GACKS. How do I remove the spaces from this?
>> > >
>> > > Thanks,
>> > > Barb Reinhardt
>> >
>> >
>> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks