Problem trying to us a range variable as an array variable

  • Thread starter Thread starter TBA
  • Start date Start date
T

TBA

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.
 
ActiveCell.Address returns a string, not a range, which is why you get the
error. Dim your variable as String.

HTH,
Shockley
 
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 restore:

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