Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prob with Var Data-types..
I have this macro to assign rows with data headers to the variable HderRows
- I want to return HderRows as a Row number or Cell though can't get it to work..missing something here about Object variables or something. - Not sure why Dim HderRows () doesn't allow empty parenthesis. Understand this should give a variable array (though 15 is a reasonable max anyway). Thanks M. Dim HderRows(15) As String NoHderRows = 1 Do Until IsEmpty(ActiveCell) ActiveCell.Offset(CntMon + 5, 0).Select Loop NoHderRows = NoHderRows - 1 'Defines HeaderRows Range("B3").Select For j = 1 To NoHderRows HderRows(j) = ActiveCell.Value ActiveCell.Offset(CntMon + 5, 0).Select Next j |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prob with Var Data-types..
You have 2 problems:
First is that the do while loop might possible expand past the array value. Secondly, you can use Dim HderRows() As String to set an unknown array size but you must at some point use the redim to reset the array size BEFORE you start using the array Good Luck, Stewart Rogers "Mourinho" wrote: I have this macro to assign rows with data headers to the variable HderRows - I want to return HderRows as a Row number or Cell though can't get it to work..missing something here about Object variables or something. - Not sure why Dim HderRows () doesn't allow empty parenthesis. Understand this should give a variable array (though 15 is a reasonable max anyway). Thanks M. Dim HderRows(15) As String NoHderRows = 1 Do Until IsEmpty(ActiveCell) ActiveCell.Offset(CntMon + 5, 0).Select Loop NoHderRows = NoHderRows - 1 'Defines HeaderRows Range("B3").Select For j = 1 To NoHderRows HderRows(j) = ActiveCell.Value ActiveCell.Offset(CntMon + 5, 0).Select Next j |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prob with Var Data-types..
Thanks Stewart
ReDim...of course. I managed to delete an important line in the code I pasted- here it is again. I still cannot get the HderRows variable (in the 2nd part of the code) to return a row or cell value however...I am not sure what to apply in terms of var data-type and/or cell property (.Value?) for the loop to return a row or cell for each HderRows variable. All my attempts are returning errors or unwanted values. M. 'Defines Number of Header Rows NoHderRows = 1 Do Until IsEmpty(ActiveCell) ActiveCell.Offset(CntMon + 5, 0).Select (MISSING) NoHderRows = NoHderRows + 1 Loop NoHderRows = NoHderRows - 1 'Defines HeaderRows Range("B3").Select For j = 1 To NoHderRows HderRows(j) = ActiveCell.Select ActiveCell.Offset(CntMon + 5, 0).Select Next j "Datasort" wrote: You have 2 problems: First is that the do while loop might possible expand past the array value. Secondly, you can use Dim HderRows() As String to set an unknown array size but you must at some point use the redim to reset the array size BEFORE you start using the array Good Luck, Stewart Rogers "Mourinho" wrote: I have this macro to assign rows with data headers to the variable HderRows - I want to return HderRows as a Row number or Cell though can't get it to work..missing something here about Object variables or something. - Not sure why Dim HderRows () doesn't allow empty parenthesis. Understand this should give a variable array (though 15 is a reasonable max anyway). Thanks M. Dim HderRows(15) As String NoHderRows = 1 Do Until IsEmpty(ActiveCell) ActiveCell.Offset(CntMon + 5, 0).Select Loop NoHderRows = NoHderRows - 1 'Defines HeaderRows Range("B3").Select For j = 1 To NoHderRows HderRows(j) = ActiveCell.Value ActiveCell.Offset(CntMon + 5, 0).Select Next j |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 error "some chart types cannot be combined with other chart types. Select a different chart types" | Charts and Charting in Excel | |||
Prob with ChartSource DataData Range | Charts and Charting in Excel | |||
Data types when importing Excel data to SQLServer | Excel Discussion (Misc queries) | |||
Urgent! Prob with Excel 2002 Template Wizard with Data Tracking ad | Excel Discussion (Misc queries) | |||
Importing data from Access into Excel: prob w/ cutting off fields | Excel Worksheet Functions |