angakis

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))

range(rng).select

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

Angelos

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))

range(rng).select

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

Angelos