|
Anthony's VBA Forum Where the knowledge is shared |
|
|
View previous topic :: View next topic |
Author |
Message |
angakis
Joined: 20 Dec 2008 Posts: 1
|
Posted: Sat Dec 20, 2008 9:20 am Post subject: Use string variable as Range object |
|
|
Hi there,
I have a problem.
I try to create a string variable (strg) to use as a range boject:
[code]Sub CellAddress()
Dim cellz(2)
Dim strg As String
cellz(1) = CStr(Cells(1, 1).Address)
cellz(2) = CStr(Cells(2, 2).Address)
strg = """" & cellz(1) & """" & "&"",""&" & """" & cellz(2) & """"
Range(strg).Select
End Sub[/code]
if you debug.print (strg) you ll see it produces "A1"&","&"A2"
However, this sub produces an error. If instead i write
Range("A1"&","&"A2") or Range(cellz(1)&","&cellz(2)) which is the same thing, it works perfectly. My problem is not how to select the cells, vba can do this in many ways, but why cant i use this procedure or what should i do (maybe convert strg into some other form) since this technique could be used in many loops and a wider range where it is difficult to write the address of the cells manually.
Thanks Angelos |
|
Back to top |
|
|
Kenneth Hobson Gold Member
Joined: 25 Mar 2007 Posts: 21 Location: Tecumseh, OK
|
Posted: Wed Mar 04, 2009 10:36 pm Post subject: |
|
|
Welcome to the forum!
Of course selecting A1 and B2 as I think you want strg defined is only two cells. Is that what you wanted?
Your syntax should be:
[code]Sub CellAddress()
Dim cellz(2)
Dim strg As String
cellz(1) = CStr(Cells(1, 1).Address)
cellz(2) = CStr(Cells(2, 2).Address)
strg = cellz(1) & "," & cellz(2)
Range(strg).Select
End Sub[/code]
Select is seldom needed. |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2002 phpBB Group
|