Using counter variable as cell reference

Hi Brian,
I can't tell what program you are using - you should update your profile so we can tell.

In Excel VBA the way to run through a bunch of cellis is - here is an example

Please Login or Register  to view this content.

I hope this shows you what you need.

Originally Posted by MarvinP

Hi Brian,
I can't tell what program you are using - you should update your profile so we can tell.

MArvin. sorry about that. i'll try to get it better up to speed. I run both 2004 and 2008 on a Mac but all of programming experience other than direct cell programming dates to Excel 2.2 for the Mac.

The Virtual Basic stuff is like greek to me although alot of the functions cross.

But assuming I can divine some meaning, my goal here is to find the next cell down the column from my current selection that does NOT match the content of the current selection.

I cannot seem to get any of these macros to run by pasting this stuff in virtual basic.I always seem to get errors or off with the clock running. the only macros I've been able to get to work are the old macro sheets I still have around from Excel 2.2.

the later versions still recognize these as macros and carries out the functions in them so I assume that between the Macro command and the VBE you get more or less a similar compiled macro. I just do not seem to be able to carry it off in the VBE. If there is a good tutorial, i'm glad to start from scratch although I still have luck rewriting these old macros to perform current selection tasks.

Forgetting what platform I'm using for writing, just taking the code you offered, what do I have to do to substitute the content of the currently selected sell for the test string "Is this It?".

Then the next problem is that I don't know how many cells I want to run through looking. you gave for RowCtr 1-100 for instance. Rather I want to keep looking (by column, i.e. running down the column of the selection) for the first cell that doesn't match.

And I'm only really trying to count rows because the info I'm matching is all in one column. It looks as if you have offered a generic array searcher for a two dimensional application, which is helpful to my understanding but I assume I could do without a ColCtr.

Then once I find the first cell in the column that Doesn't match, I want to select it. So I assume I need some kind of language that can turn RowCtr into a relative reference, e.g.

SELECT(R["RowCtr"]C]

although that is old syntax as it seems Virtual basic doesn't use the capitalized functions. Don't know if the variable name needs quotes but obviously I'm trying to call the numerical value of the variable as a relative row reference

Finally, what does DIM RowCtr As Double do?

Thanks for dropping in. Hope I was clear enough in trying to integrate your suggestions and the questions I have about them.

Brian

Hi Brian,
As soon as I saw "Mac" I knew my answers were wrong. I'm on a Microsoft Windows machine, running Office for the PC, not Mac. I think Microsoft didn't do Visual Basic for Applications (VBA) for the Mac Office versions in 2007. I do believe they will put it back in for the 2010 verson of Mac Office. See:
http://www.informationweek.com/news/...leID=208803359
When VBA behind Mac Office 2010 appears you should get a lot closer to having our PC code working.

I think you should wait for the Mac 2010 Office with Excel before you start your learning.

Sorry for the confusion. You need to back into your profle and tell us what version of Excel you are using. Look at other posts and it will show. Your profile now says Rhode Island but not version of Excel.

Sorry - I guess I can't help.

Originally Posted by romperstomper

There are probably better ways, but:

Please Login or Register  to view this content.

should work, I think.

Brilliant. No such thing as a better way, and glad to find someone who can give me cell code until I get VB friendly.

Works like a charm and my apologies, I mentioned in the new thread that I left this one but I should have come back and posted the link. I really appreciate the follow through and was unsure of politenesses where I felt that my question had changed. While one can change the title of their response to a thread, I was trying to think of how to help better title the thread for the question I had. So my apologies for spreading this out when you were just about to bring it together.

I thought maybe ACTIVE.CELL would work but did not know about the OFFSET function. otherwise I would have still need and R[1]C[1] reference in onehalf of the IF formula.

In my text on macro language which is Excel 2.2, there is no END.IF and the IF function is entered with all its arguments in a single cell as you might find in a worksheet nowadays.

But your columnar form works fine, I pasted it in. Redefined the length of the macro using the INSERT>NAME>DEFINE command. And it was off to the races.

In terms of understanding how it works, I'm missing one piece of logic as to which of the cells after the IF in this kind of construction is the true or false result, or if the IF/END.IF just continues until it gets a true result.

The relevant code lines are

Please Login or Register  to view this content.

So the first cell is the test argument with no results arguments. It is looking for a mismatch between the selected cell and that offset below it by the counter variable of the loop. My confusion is that I would have assumed the 1st macro cell down would be the true value. That is the cell whose function is to move the active cell selection down the sheet. But that movement ceases when the test argument is true, so it seems that my assumption that this cell is the true value and BREAK function is the false value is not a correcting reading of this IF/END.IF approach.

So maybe the way this works from my guesswork approach is that a True result for the argument makes the macro jumot to the END.IF, and the False value causes it just to contineu through SELECTing the next row in that column and then the BREAK function causes it to jump to the NEXT() command and the loop repeats.

The problem with that anaylsis is, how does the true result leading to END.IF get by the NEXT command following it so that the loop stops?

Brian

Originally Posted by romperstomper

the BREAK()
tells it to jump out of the loop once the TRUE part is reached. (Since we aren't really interested in doing anything if the condition is not met, there is no ELSE part in this example)

R,

Got it. So the BREAK() Function jumps out of the FOR/NEXT loop, Not just out of the IF. That is what I missed.

BTW,

is my cultural clock as retro as my coding or is that handle a reference to Romper Room.

Brian

Originally Posted by romperstomper

There are probably better ways, but:

Please Login or Register  to view this content.

should work, I think.

RS -
I know I marked this solved, but I noticed one thing I forgot to ask. If the True Value is in the code lines between the IF and END.IF (because ELSE is omitted it is only true values), what are there still ltrue and false values in the function line for the IF?

Could you omit those, and did you put them in for the purpose of evaluating that line of code and the state of counter at that point while debugging, but they otherwise do not affect the actual operation of the whole macro?

Thanks,

Brian.

PS - guess your new Avatar is a little more laid back (unless that kid has a tattoo somewhere)

Originally Posted by romperstomper

There aren't true or false parts in the IF function.

My bad, those are the arguments for OFFSET.

Brian