Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Finding max array value of variable cell range | Excel Discussion (Misc queries) | |||
Variable range addresses for use in array functions | Excel Discussion (Misc queries) | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |