what wrong here, range Cell as counter

[John]

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

 

[Barb Reinhardt]

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

 

[Dave Peterson]

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.

 

[Dave Peterson]

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]

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

 

[John]

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

 

[Dave Peterson]

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.