Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

look at the trim function it may help

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

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

Removing spaces from value using VBA
 

Here we go ...
rangename = Replace(Workbooks(curbook).Worksheets(datasheet).R ange("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" wrote in message
...
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" wrote in
message
...
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

Removing spaces from value using VBA
 

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