Problem trying to us a range variable as an array variable
 

Below is a bit of code I use to store header values into a one-dimensional
array, along with the some less important information like the column width
of the column each header is in (it's important to have this, trust me
heh). All variables have been declared.

TblWidth = 0

For Each Cell In Range("Database").Rows(1).Cells
TblWidth = TblWidth + 1
Headers(TblWidth) = Cell.Value
HeaderWidths(TblWidth) = Cell.ColumnWidth
Next Cell

What I'd also like to do is create a one-dimensional Range array variable,
say HeadersRng(a), that will store the location of each header as a range.
I've tried this:

Dim HeadersRng(100) as Range
Range("Database").Rows(1).Columns(1).Select

For a = 1 To TblWidth
HeadersRng(a) = ActiveCell.Address
ActiveCell.Offset(0, 1).Select
Next a

I'm getting a Run-time error '91': Object variable or With block variable
not set.

I realize there may be a better way to loop through this process, perhaps
even in the first loop I referenced. However, I feel that I am missing
something fundamental here. Am I close? Any and all help appreciated.
Thanks!

-gk-

PS this group rocks! thanks so much for all the help I've received so far.

Problem trying to us a range variable as an array variable
 

ActiveCell.Address returns a string, not a range, which is why you get the
error. Dim your variable as String.

HTH,
Shockley



"TBA" wrote in message
...
Below is a bit of code I use to store header values into a one-dimensional
array, along with the some less important information like the column
width
of the column each header is in (it's important to have this, trust me
heh). All variables have been declared.

TblWidth = 0

For Each Cell In Range("Database").Rows(1).Cells
TblWidth = TblWidth + 1
Headers(TblWidth) = Cell.Value
HeaderWidths(TblWidth) = Cell.ColumnWidth
Next Cell

What I'd also like to do is create a one-dimensional Range array variable,
say HeadersRng(a), that will store the location of each header as a range.
I've tried this:

Dim HeadersRng(100) as Range
Range("Database").Rows(1).Columns(1).Select

For a = 1 To TblWidth
HeadersRng(a) = ActiveCell.Address
ActiveCell.Offset(0, 1).Select
Next a

I'm getting a Run-time error '91': Object variable or With block variable
not set.

I realize there may be a better way to loop through this process, perhaps
even in the first loop I referenced. However, I feel that I am missing
something fundamental here. Am I close? Any and all help appreciated.
Thanks!

-gk-

PS this group rocks! thanks so much for all the help I've received so
far.

Problem trying to us a range variable as an array variable
 

But I want a range, and changing it to HeadersRng(a) = ActiveCell doesn't
work either.

-gk-


"shockley" wrote in message
...
ActiveCell.Address returns a string, not a range, which is why you get the
error. Dim your variable as String.

HTH,
Shockley



"TBA" wrote in message
...
Below is a bit of code I use to store header values into a
one-dimensional
array, along with the some less important information like the column
width
of the column each header is in (it's important to have this, trust me
heh). All variables have been declared.

TblWidth = 0

For Each Cell In Range("Database").Rows(1).Cells
TblWidth = TblWidth + 1
Headers(TblWidth) = Cell.Value
HeaderWidths(TblWidth) = Cell.ColumnWidth
Next Cell

What I'd also like to do is create a one-dimensional Range array
variable,
say HeadersRng(a), that will store the location of each header as a
range.
I've tried this:

Dim HeadersRng(100) as Range
Range("Database").Rows(1).Columns(1).Select

For a = 1 To TblWidth
HeadersRng(a) = ActiveCell.Address
ActiveCell.Offset(0, 1).Select
Next a

I'm getting a Run-time error '91': Object variable or With block
variable
not set.

I realize there may be a better way to loop through this process,
perhaps
even in the first loop I referenced. However, I feel that I am missing
something fundamental here. Am I close? Any and all help appreciated.
Thanks!

-gk-

PS this group rocks! thanks so much for all the help I've received so
far.

Problem trying to us a range variable as an array variable
 

Since you're looping through each cell in row 1 of the table anyway,
why not just save that partial row as a range? Then you can save all
the values at once in a variant variable, and assign them back in
one step. For instance:

Dim headerRange As Range
Dim headers As Variant
Dim headerWidths() As Double
Dim i As Integer

Set headerRange = Range("Database").Resize(1)
With headerRange
headers = .Value
ReDim headerWidths(1 To .Count)
For i = 1 To .Count
headerWidths(i) = .Cells(1, i).ColumnWidth
Next i
End With

Then, when you need to resto

With headerRange
.Value = headers
For i = 1 To headerRange.Count
.Cells(1, i).ColumnWidth = headerWidths(i)
Next i
End With

note that headers will be a variant that contains a two-dimensional
one-based array, so that the value of the third header can be
accessed as

headers(1, 3)




In article ,
"TBA" wrote:

But I want a range, and changing it to HeadersRng(a) = ActiveCell doesn't
work either.

-gk-


"shockley" wrote in message
...
ActiveCell.Address returns a string, not a range, which is why you get the
error. Dim your variable as String.

HTH,
Shockley



"TBA" wrote in message
...
Below is a bit of code I use to store header values into a
one-dimensional
array, along with the some less important information like the column
width
of the column each header is in (it's important to have this, trust me
heh). All variables have been declared.

TblWidth = 0

For Each Cell In Range("Database").Rows(1).Cells
TblWidth = TblWidth + 1
Headers(TblWidth) = Cell.Value
HeaderWidths(TblWidth) = Cell.ColumnWidth
Next Cell

What I'd also like to do is create a one-dimensional Range array
variable,
say HeadersRng(a), that will store the location of each header as a
range.
I've tried this:

Dim HeadersRng(100) as Range
Range("Database").Rows(1).Columns(1).Select

For a = 1 To TblWidth
HeadersRng(a) = ActiveCell.Address
ActiveCell.Offset(0, 1).Select
Next a

I'm getting a Run-time error '91': Object variable or With block
variable
not set.

I realize there may be a better way to loop through this process,
perhaps
even in the first loop I referenced. However, I feel that I am missing
something fundamental here. Am I close? Any and all help appreciated.
Thanks!

-gk-

PS this group rocks! thanks so much for all the help I've received so
far.

Problem trying to us a range variable as an array variable
 

Try:

Set HeadersRng(a) = ActiveCell



"TBA" wrote in message
...
But I want a range, and changing it to HeadersRng(a) = ActiveCell doesn't
work either.

-gk-


"shockley" wrote in message
...
ActiveCell.Address returns a string, not a range, which is why you get
the
error. Dim your variable as String.

HTH,
Shockley



"TBA" wrote in message
...
Below is a bit of code I use to store header values into a
one-dimensional
array, along with the some less important information like the column
width
of the column each header is in (it's important to have this, trust me
heh). All variables have been declared.

TblWidth = 0

For Each Cell In Range("Database").Rows(1).Cells
TblWidth = TblWidth + 1
Headers(TblWidth) = Cell.Value
HeaderWidths(TblWidth) = Cell.ColumnWidth
Next Cell

What I'd also like to do is create a one-dimensional Range array
variable,
say HeadersRng(a), that will store the location of each header as a
range.
I've tried this:

Dim HeadersRng(100) as Range
Range("Database").Rows(1).Columns(1).Select

For a = 1 To TblWidth
HeadersRng(a) = ActiveCell.Address
ActiveCell.Offset(0, 1).Select
Next a

I'm getting a Run-time error '91': Object variable or With block
variable
not set.

I realize there may be a better way to loop through this process,
perhaps
even in the first loop I referenced. However, I feel that I am
missing
something fundamental here. Am I close? Any and all help
appreciated.
Thanks!

-gk-

PS this group rocks! thanks so much for all the help I've received
so
far.