Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
In the range, MYRange I want each cell to have it's own little subrange
to search. I try and do it below. When it gets to Subrange(cell)= it uses the value of cell instead of cell as a range and delivers an out of range error (or identifies the incorrect range using the Cell value). Thanks John ------------------------- Dim Cell as Range Dim Myrange as range, Anotherrange as Range etc. Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range For Each Cell in MyRange Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
Cell is already used within VBA, so I'd avoid it.
Dim myCell as Excel.Range Dim Myrange as Excel.range, Dun Anotherrange as Excel.Range Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range myCount = 0 For Each myCell in MyRange 'Not sure what you're trying to do here. 'Let us know and we can help some more myCount = myCount + 1 Set AnotherRange = myCell.offset(1,0) '<~~~do what you want here Set Subrange(myCount)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next myCells HTH, Barb Reinhardt "John" wrote: In the range, MYRange I want each cell to have it's own little subrange to search. I try and do it below. When it gets to Subrange(cell)= it uses the value of cell instead of cell as a range and delivers an out of range error (or identifies the incorrect range using the Cell value). Thanks John ------------------------- Dim Cell as Range Dim Myrange as range, Anotherrange as Range etc. Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range For Each Cell in MyRange Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
I do my best to avoid naming a variable Cell. But VBA doesn't have any keyword
named Cell. But it does have Cells, though. Barb Reinhardt wrote: Cell is already used within VBA, so I'd avoid it. Dim myCell as Excel.Range Dim Myrange as Excel.range, Dun Anotherrange as Excel.Range Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range myCount = 0 For Each myCell in MyRange 'Not sure what you're trying to do here. 'Let us know and we can help some more myCount = myCount + 1 Set AnotherRange = myCell.offset(1,0) '<~~~do what you want here Set Subrange(myCount)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next myCells HTH, Barb Reinhardt "John" wrote: In the range, MYRange I want each cell to have it's own little subrange to search. I try and do it below. When it gets to Subrange(cell)= it uses the value of cell instead of cell as a range and delivers an out of range error (or identifies the incorrect range using the Cell value). Thanks John ------------------------- Dim Cell as Range Dim Myrange as range, Anotherrange as Range etc. Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range For Each Cell in MyRange Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
If I have:
Dim myRng as range set myRng = somerange Then myrng(x,y) is shorthand for myrng.cells(x,y) where x and y are numbers. Chip Pearson has some notes written by Alan Beban: http://www.cpearson.com/excel/cells.htm It may be an interesting read for you. So my question is what did you mean by this: Subrange(cell) Since you're not providing any property, excel's vba "knows" you want its default property--value in this case: subrange(cell.value) And if cell.value isn't numeric, you're gonna have trouble. ===== And I'm not sure what you're doing, but you'll probably need a Set statement for this: Set Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) John wrote: In the range, MYRange I want each cell to have it's own little subrange to search. I try and do it below. When it gets to Subrange(cell)= it uses the value of cell instead of cell as a range and delivers an out of range error (or identifies the incorrect range using the Cell value). Thanks John ------------------------- Dim Cell as Range Dim Myrange as range, Anotherrange as Range etc. Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range For Each Cell in MyRange Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
That loads everything but later:
I end up finding a cell as range using find, say it is "First" as range. Now I want to search the range represented by subrange(mycount) for certain things but I don't know what the "mycount" is, only the cell range, "First." I would want to do subrange(First).Find etc. etc. But the range subrange() is looking for a numerical "mycount", not a range. When I stick the range in it uses the value of the range. So I'm stuck again unless I carry along a 2 dimensional array that matches cell ranges to mycounts or I could have a formula that uses row/column of First to calculate mycount but then its so untidy I might as well go back to regular for/next loops using basic cells notation instead of range notation. I had it working. I just wanted to make it a little faster and neater. John Barb Reinhardt wrote: Cell is already used within VBA, so I'd avoid it. Dim myCell as Excel.Range Dim Myrange as Excel.range, Dun Anotherrange as Excel.Range Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range myCount = 0 For Each myCell in MyRange 'Not sure what you're trying to do here. 'Let us know and we can help some more myCount = myCount + 1 Set AnotherRange = myCell.offset(1,0) '<~~~do what you want here Set Subrange(myCount)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next myCells HTH, Barb Reinhardt "John" wrote: In the range, MYRange I want each cell to have it's own little subrange to search. I try and do it below. When it gets to Subrange(cell)= it uses the value of cell instead of cell as a range and delivers an out of range error (or identifies the incorrect range using the Cell value). Thanks John ------------------------- Dim Cell as Range Dim Myrange as range, Anotherrange as Range etc. Set Myrange=(whatever)) Set Anotherrange= a range with offsets of cell etc. Dim Subrange(100) as Range For Each Cell in MyRange Subrange(Cell)=Union(AnotherRange, YetAnotherrange, AndAnotherrange) Next |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
Dave Peterson wrote:
This is the confusing question. I want cell to reer to a particular cell range in MyRange. Using the I want Subrange(Cell) to fer to another range. For instance say Myrange=Cells((1,1),Cells(100,100)) Mycell=cells(10,10) within that range Mcol(Mycel)= the range of cells in the column Mycel is part of within Myrange. Myrow(MyCell) is the range of cells in the row that Mycell is part of. Doing a for next I have set subrange(Mycell)=union(SubRow(mycount), SubCol(mycount). This is the column and row coming out of Cell Mycell for every Cell in Myrange. Now... when I hit Mycell I want to count, say, all the 1's in the column and row of Mycell. I do it by countif using subrange(MyCell) except it doesn't work. I can't use subrange(mycount) because I don't know what mycount is at this point. The actual subranges are much more complicated than jut row and column... this is just for example. It just seems excel used Mycell as a range sometime and as a value sometimes and that isn't controllable. John So my question is what did you mean by this: Subrange(cell) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
what wrong here, range Cell as counter
I'm not sure I understand, but...
Dim myRng as range dim myCell as range dim mySubRngCol as range dim mySubRngRow as range with worksheets("Somesheet") set myrng = .range("a1:CV100") end with 'for columns for each mysubrngcol in myrng.columns for each mycell in mysubrngcol.cells msgbox mycell.address next mycell next mysubrngcol 'or for rows... for each mysubrngrow in myrng.rows for each mycell in mysubrngrow.cells msgbox mycell.address next mycell next mysubrngrow ========= You could also use something like: dim myCell2 as range dim myCell as range dim myrng as range 'same kind of assignments for each mycell in myrng.cells for each mycell2 in intersect(mycell.entirecolumn, myrng).cells msgbox mycell2.address next mycell2 next mycell And use mycell.entirerow to do the rows. John wrote: Dave Peterson wrote: This is the confusing question. I want cell to reer to a particular cell range in MyRange. Using the I want Subrange(Cell) to fer to another range. For instance say Myrange=Cells((1,1),Cells(100,100)) Mycell=cells(10,10) within that range Mcol(Mycel)= the range of cells in the column Mycel is part of within Myrange. Myrow(MyCell) is the range of cells in the row that Mycell is part of. Doing a for next I have set subrange(Mycell)=union(SubRow(mycount), SubCol(mycount). This is the column and row coming out of Cell Mycell for every Cell in Myrange. Now... when I hit Mycell I want to count, say, all the 1's in the column and row of Mycell. I do it by countif using subrange(MyCell) except it doesn't work. I can't use subrange(mycount) because I don't know what mycount is at this point. The actual subranges are much more complicated than jut row and column... this is just for example. It just seems excel used Mycell as a range sometime and as a value sometimes and that isn't controllable. John So my question is what did you mean by this: Subrange(cell) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using for loop counter as a cell reference | Excel Programming | |||
How do I set up a counter cell? | Excel Worksheet Functions | |||
how do I make a counter cell in excel | Excel Programming | |||
Cell counter | Excel Discussion (Misc queries) | |||
Macro selecting wrong cell range | Excel Programming |