Excel Ranges

[Bartender]

Alright, I have a range that I want the user to be able to change. Really, its an index of cells which my program references.

Right now I have:

Sheets("Master").Select
  MaxCount = Application.CountA(Range("A50", "A500"))
  Range("B50").Select
  Set IndexRng = Range(ActiveCell, ActiveCell.Offset(MaxCount - 1, MaxCount - 1))

And later:

CurVal = CurVal + Application.Index([Master!IndexRng], Cur(i), Cur(i - 1))

When I changed the range from "B50:AL88" to IndexRng in the statement directly above, I get the error "Type Mismatch (Error 13)", and I'm not sure whats wrong.
Thanks in advance.

[RobDog888]

Could be either two things. One your data in the range contains an invalid data type for your formatted range. A letter in an
numeric field, etc. Or your parameters for .Index are mismatched?

If you move the range back it still works, correct?

[Bartender]

Rob,
Yes, if I change Master!IndexRng to Master!B50:AL86, the code works.

Does the range variable store the *actual* contents of the cells, or does it just store the equivalent of B50:AL86? The latter is what I want.

Also, I posted a problem in a thread a few below this, to reset my code before or after each use. Do you have any insights on that?

Thanks!

[RobDog888]

The Range object represents a selection of cells so its probably safe to assume that it represents the data and not just the
string begin/end designators.

[Bartender]

Rob,
How can I make it so that the index function:

Application.Index([Master!IndexRng], Cur(i), Cur(i - 1))

has a value in indexrng that I don't need to set, but rather that self selects. That's all I'm trying to do here. I don't want to 'hard code' in the size of the index, as in saying "B2:AL89", but rather I want to make it so that the index range is flexible and self counting. I tried to do that in the code in my original post, but I guess I failed. How should I have coded it? I'm quite inexperienced with programming.
Also, I'm not 100% sure that my "Set IndexRng..." is working right, so my problem could be there as well.
Thanks for your time.

[Bartender]

Alright, I'm still interested in the answer, but I am trying to move on so....
I have set the range to "B50:GS200" in order to allow plenty of room for expansion of the index from its current size, and it doesn't seem to run any slower than when I had it set to "B50:AL86" mainly, I think, because it still only calls to the first 38 references in the index.
Is there anything wrong with this? Am I shooting myself in the foot when this goes to 300,000 iterations rather than just the 5000 that I tested it at?

Thanks for your help.

[RobDog888]

If your index is only needing to beidentified as a used range then no need to specify the large range and loop. You could place it on
its own sheet and use the Cells.SpecialCells(xlCellTypeLastCell).Row to find the last cell with no looping.

[Bartender]

Sorry Rob,
I was misleading with what I said. The loop is not identifying the index, the loop is the program, and the program iterates through this loop between 720 to 320,000 times depending on user input. This loop itself actually references a value in the index between 5 and 9 times each time the loop is called, so the index is really being used up to 2 and a half million times or so per run of the program. This is why I was concerned if hard coding the actual index size to be much bigger than what I needed would slow me down.
So my index is really a 37x37 block, but I've made it so the program thinks the index is a 160x160 block or so, so users can expand the index... and I was hoping this wouldn't affect the running time of the program, since the inputs are still only calling the first 37x37 block of the index.

I would try to do what you suggested, but I have had absolutely NO luck with getting a

Application.Index([Master!IndexRng], Cur(i), Cur(i - 1))

function to work with anything but a static range that I pre-select, ie "B50:AL87" in place of IndexRng.
[Master!IndexRng], Master!IndexRng, IndexRng, [IndexRng],[Master!ActiveCell:ActiveCell.Offset(variable,variable)], etc..... all haven't worked.
Thanks again for your time!

[RobDog888]

I was going to try some demo code but Application.Index doesnt show as taking any arguments? I'm on my first cup of
coffee so what am I missing?

[Bartender]

Rob,
Jeez, I have no idea. I know it doesnt seem to take any arguments, its never done the fancy formatting for me. But try creating a, say, 8x8 block of numbers in excel, and then use Application.Index([Sheet1!"A1:H8"],2,4) and see if it doesn't give you the value at 2 rows 4 columns.

Maybe my computer is just humoring me?
Good luck, and thanks!

[Bartender]

I noticed that I typed:
Application.Index([Sheet1!"A1:H8"],2,4)
in my post above, where I believe that should have been:
Application.Index([Sheet1!A1:H8],2,4)
Sorry