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

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

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

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