Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Macro Copy Active Cell to Range Name, loop
I am running the following code that I got from this newsgroup,
...... Sub NameMe() Dim strName As String Dim strAddr As String strName = ActiveCell.Value strAddr = ActiveCell.Address(, , xlR1C1) ActiveWorkbook.Names.Add _ Name:=strName, _ RefersToR1C1:="=Sheet1!" & strAddr End Sub ..... For some reason the Names.Add function will not accept the string. It will accept strings which I manually type, eg. "typedname", but not strings set to the ActiveCell.Value. Is there some way to work around this? Can I add quotes to the string somehow? Am I barking up the wrong tree? Any help is appreciated, Petur G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Macro Copy Active Cell to Range Name, loop
Before you go too far, how about sharing what's in Activecell.value?
Maybe it isn't a valid name???? No spaces. No names that look like addresses. No invalid characters... Debug.print "***" & activecell.value & "***" may help pinpoint the problem. " wrote: I am running the following code that I got from this newsgroup, ..... Sub NameMe() Dim strName As String Dim strAddr As String strName = ActiveCell.Value strAddr = ActiveCell.Address(, , xlR1C1) ActiveWorkbook.Names.Add _ Name:=strName, _ RefersToR1C1:="=Sheet1!" & strAddr End Sub .... For some reason the Names.Add function will not accept the string. It will accept strings which I manually type, eg. "typedname", but not strings set to the ActiveCell.Value. Is there some way to work around this? Can I add quotes to the string somehow? Am I barking up the wrong tree? Any help is appreciated, Petur G -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Macro Copy Active Cell to Range Name, loop
On Mar 29, 7:52 pm, Dave Peterson wrote:
Before you go too far, how about sharing what's in Activecell.value? Maybe it isn't a valid name???? No spaces. No names that look like addresses. No invalid characters... Debug.print "***" & activecell.value & "***" may help pinpoint the problem. " wrote: I am running the following code that I got from this newsgroup, ..... Sub NameMe() Dim strName As String Dim strAddr As String strName = ActiveCell.Value strAddr = ActiveCell.Address(, , xlR1C1) ActiveWorkbook.Names.Add _ Name:=strName, _ RefersToR1C1:="=Sheet1!" & strAddr End Sub .... For some reason the Names.Add function will not accept the string. It will accept strings which I manually type, eg. "typedname", but not strings set to the ActiveCell.Value. Is there some way to work around this? Can I add quotes to the string somehow? Am I barking up the wrong tree? Any help is appreciated, Petur G -- Dave Peterson- Hide quoted text - - Show quoted text - Thank you, Dave. You were right, there were spaces in the string. Obviously, I am new to VBA and the usenet group. For anyone else reading this, here is some code to remove spaces, which is available at: (http://www.fontstuff.com/vba/ vbatut05.htm#removespaces) Public Function RemoveSpaces(strInput As String) ' Removes all spaces from a string of text Test: If InStr(strInput, " ") = 0 Then RemoveSpaces = strInput Else strInput = Left(strInput, InStr(strInput, " ") - 1) _ & Right(strInput, Len(strInput) - InStr(strInput, " ")) GoTo Test End If End Function Thanks again, Petur G |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: Macro Copy Active Cell to Range Name, loop
Another way to remove spaces from a string:
dim myStr as string mystr = "h i th er e " mystr = replace(mystr," ","") msgbox mystr Replace was added in xl2k. before that, you could use: mystr = application.substitute(mystr," ","") " wrote: On Mar 29, 7:52 pm, Dave Peterson wrote: Before you go too far, how about sharing what's in Activecell.value? Maybe it isn't a valid name???? No spaces. No names that look like addresses. No invalid characters... Debug.print "***" & activecell.value & "***" may help pinpoint the problem. " wrote: I am running the following code that I got from this newsgroup, ..... Sub NameMe() Dim strName As String Dim strAddr As String strName = ActiveCell.Value strAddr = ActiveCell.Address(, , xlR1C1) ActiveWorkbook.Names.Add _ Name:=strName, _ RefersToR1C1:="=Sheet1!" & strAddr End Sub .... For some reason the Names.Add function will not accept the string. It will accept strings which I manually type, eg. "typedname", but not strings set to the ActiveCell.Value. Is there some way to work around this? Can I add quotes to the string somehow? Am I barking up the wrong tree? Any help is appreciated, Petur G -- Dave Peterson- Hide quoted text - - Show quoted text - Thank you, Dave. You were right, there were spaces in the string. Obviously, I am new to VBA and the usenet group. For anyone else reading this, here is some code to remove spaces, which is available at: (http://www.fontstuff.com/vba/ vbatut05.htm#removespaces) Public Function RemoveSpaces(strInput As String) ' Removes all spaces from a string of text Test: If InStr(strInput, " ") = 0 Then RemoveSpaces = strInput Else strInput = Left(strInput, InStr(strInput, " ") - 1) _ & Right(strInput, Len(strInput) - InStr(strInput, " ")) GoTo Test End If End Function Thanks again, Petur G -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy name from active sheet to cell - using macro or function | Excel Worksheet Functions | |||
need to Copy or Move to active cell from specified range | Excel Discussion (Misc queries) | |||
To copy from a range in another sheet to the active cell | Excel Programming | |||
Macro Copy Active Cell to Range Name, loop | Excel Programming | |||
Macro to copy cell data to word document based on an active row? | Excel Programming |