Pass a string to an address

[Robert H]

In this snip I need to pass a range.address of a range to a string,
manipulate the strig and then pass it back to the range.address. the
first two steps are fine but I get an error "assignment to constant
not permited" when I try to pass the string back to the address.


Dim srcRngU as Range
Dim wrkAdd As String

srcRng is assigned to a range with removed code...

wrkAdd = srcRngU.Address
wrkAdd = Application.Substitute(wrkAdd, ",", "," & shtNm & "!")
wrkAdd = shtNm & "!" & wrkAdd
srcRngU.Address = wrkAdd <-------"assignment to constant not permited"
error here

This must be something simple!!!!! but I give up
help will be appreciated

Robert

 

[JE McGimpsey]

You can't assign an address to a range, the range's address is an
inherent property.

Looks like you really want to reassign the srcRngU range object variable
to a new range on sheet shtNm. Perhaps something like:

Set srcRngU = Worksheets(shtNm).Range(srcRngU.Address)

 

[Robert H]

Thanks, for clarifying that to me. But,

Set srcRngU = Worksheets(shtNm).Range(srcRngU.Address)

doesn't do anything. All i can see that it would do is change the
value of srcRngU to be the same as it address string. I need to charge
the actual range of cells that the range refers to. If I read that
right your saying that cant be done. if that were the case then we
could not programaticaly create a range. Ow, my head hurts

 

[Robert H]

I answered my question with this test:

Public Sub changerange()
Dim myStrg As String
Dim myrange As Range
myStrg = "A3"

Set myrange = Range("a1")
myrange.Select

Set myrange = Range("a2")
myrange.Select

Set myrange = Range(myStrg)
myrange.Select

End Sub

sometimes I have to go back to the basics, WAY back, that is