Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Spaces | Excel Discussion (Misc queries) | |||
removing all spaces | Excel Discussion (Misc queries) | |||
Removing spaces from cells not possible? | Excel Discussion (Misc queries) | |||
REMOVING TRAILING SPACES | Excel Discussion (Misc queries) | |||
removing spaces | Excel Discussion (Misc queries) |