View Full Version : String Variable used to define a Range object (following my previous post)

12-19-2008, 12:03 PM
First of all, thanks for the warm welcome. I was amazed to see almost 20 replies to my thread after such a short time.
However my main problem was that after a not so straight forward procedure, i ended up with a case where i needed to input a string variable in a range. Just a quick reminder for the shake of better understanding:

Sub Select_NonEmpty_Cells()
Dim rng As String
Dim fin As String
Dim Cellz(1500)
Dim myrng(1500)
myrng(0) = ""
Set myrange = Range("A1:f10")
col = myrange.Columns.Count
rws = myrange.Rows.Count
k = 0
n = -1
For i = 1 To rws
For j = 1 To col
If Cells(i, j).Value <> 0 Then
k = k + 1
n = n + 1
Cells(i, j).Activate
Cellz(k) = ActiveCell.Address
myrng(k) = myrng(n) & "Cellz(" & Str(k) & ")&"",""&"
End If
Next j
Next i
myrng(k) = Left(myrng(k), Len(myrng(k)) - 5)
rng = CStr(myrng(k))

End Sub

So i use an array (myrng) which stores text.
If you debug.print(rng) will produce : Cellz( 1)&","&Cellz( 2)...Cellz(16)
This produces an error as it seems i cant use range(rng)

However, if Instead rng is directly defined as :

rng = Cellz(1) & "," & Cellz(2) & "," & Cellz(3)...

range(rng).select works Fine

Can anyone show me why this does not work and how could it be valid. I think that it would be very useful to use an array constructed in loops and not directly defined to construct a string variable which could be used.

Thanks again