old (Excel 2.x) macro R1C1 reference to active cell

**riwiseuse** · 10-18-2010, 10:32 AM

Mea Culpa. My fault for going in two directions at once. Had help with a solution at original thread when I thought that line of reasoning had petered out.

I got into this conversation in another thread and I'm not trying to jump the shark with a new title but I realized it is more important to highlight that I need help with retro language than the particular problem. That other thread isn't 'solved' but I think it started down a dead end. I don't know how to mark it canceled or superceded. I don't want to violate politienesses here or in any way spurn the helpful advances of moderaters, gurus, and just plain interested folks, so if I have committed any faux pas I apologize.

I'm having great grief getting any macros working using the Visual Basic Editor so I make all my macros by opening an old macro sheet I made in Excel 2.2 for Mac and editing a macro that already has defined name and command key.

These macros still work fine in Excel 2004 for the Mac so I'm guessing they compile and run along the same lines as the VBE.

I'm told the next version for Mac will include yet another update to the Virtual Basic approach so it might be wise to keep my antique powder dry and try to learn that when it comes out, although I'm open to any pointers to a Visual Basic tutorial for Mac 2004 (I also have 2008 for the Mac).

Meantime, if you're game I'm having trouble getting a reliable reference to the value of the active or current cell as a comparator for an IF function.

I've tried

Please Login or Register  to view this content.

I've tried substituting several alternatives that I think refer to the value of the current selection as well, e.g.

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

and tried various forms of DEREF

no matter what RC reference style or Function I have used to try to call the value of the active cell, I repeatedly get a false value for the IF function even when the next cell in the same column is equal to the active cell and I am looking for a true value.

I'm using the R1C1 style so I can get the relative reference. I want to run a FOR/NEXT loop to keep going until I get a false value but I can't get the IF function working right.

So, if you still remember the good 'ole days and can help me with the syntax, I remember that getting the R1C1 stuff right was always tough.

Brian

I

**royUK** · 10-18-2010, 10:40 AM

Please amend your closing Code Tags

[/code]

not

[code/]

**riwiseuse** · 10-18-2010, 01:47 PM

[QUOTE=royUK;2401772]Please amend your closing Code Tags

done, thanks. hope that makes this an easier read question.

brian

**royUK** · 10-18-2010, 01:53 PM

Thanks for taking the time to read and comply with the rule

.

**mikerickson** · 10-18-2010, 02:58 PM

The R1C1 reference for the cell that is holding the formula would be RC.
However, putting a formula with that reference in a cell would likely cause a Circular Reference error.

Actually, what are those lines?
Are they formulas in cells, if so Selection is not avaliable.
If they are VB code in a module, the IF doesn't work that way.

**riwiseuse** · 10-18-2010, 07:45 PM

Originally Posted by mikerickson

The R1C1 reference for the cell that is holding the formula would be RC.
However, putting a formula with that reference in a cell would likely cause a Circular Reference error.

Actually, what are those lines?
Are they formulas in cells, if so Selection is not avaliable.
If they are VB code in a module, the IF doesn't work that way.

This is macro code from the old days where you wrote it just like cell code in a column of cells. So the syntax for IF is the same as if you were just making a single cell formula.

Actually, you might still be able to write this in Excel for Mac 2004 because you can save a worksheet as an Excel Macro sheet, i.e. .xlm (Excel 4.0) or .xlw (Excel 2.2). These old macro sheets I have are actually .xls .

Anyway, I think it is futzy with R1C1, really picky because the following code in one of these macros works fine

Please Login or Register  to view this content.

So if you think about the problem from a perspective of cell coding rather than macro writing it might free your mind. I've used plenty of R1C1 references in these macros and never used RC, but have used R[1]C. It wants " " around the references, and you they are supposed to be able to calculate the references as well so you can concatenate references theoretically such as "R["&variablename&"]C3" and or substitute some calculated number from a cell for variable name.

It is just finicky and not only do I hope to use those calculated references on occasion but I can't get a relative reference to the active cell out of th A1 style. Maybe I can if I have a function that can return the A1 reference and then paste its relatively, the same way when you paste a formula entered in C1 such as =A1+B1 into C2 it changes to =A2+B2. So maybe I'm missing a way I can dispense with R1C1.

Anyway, thanks for humoring a fossil.

Brian

**riwiseuse** · 10-19-2010, 09:07 AM

I did some more experiments with code and this is really looking like a problem of the test argument in the IF function.

It is recognizing my "R1C1" entries as text because it is not necessarily looking for a cell reference argument, whereas the true and false results where I'm using the SELECTION() function work fine with the "R1C1" referencew approach.

For example the code I posted above in A1 format that works:

Please Login or Register  to view this content.

works identically if written as below and cell C146 is selected before calling the macro:

Please Login or Register  to view this content.

The code I want, at least metaphorically is:

Please Login or Register  to view this content.

The only way I can get something written like that to return a true value is if that test function is treated as a text comparison, not a cell comparison - which doesn't accomplish what I need, e.g. the following returns true as a tautology:

Please Login or Register  to view this content.

I'm going to crosspost a question on this language at Excel Banter, will return as soon as I have a link. This is the last glitch in putting to work a number of solutions you have helped me craft.

I will continue to experiment with other calls for the TEST function but if you have any other ideas of approaches to the problem that I might be able to adapt in this archaic language I'm all ears . . . eer eyes.

I'm trying to run down a column looking for the first value that does not match. I had placed the above codes in a FOR/NEXT LOOP and was actually selecting "R[1]C" for the true or match value and had placed a RETURN() to stop the macro in the false value, but because the stumbling block has been getting a working IF test that responds to my location on the currently active sheet I have left out that other language in my examples above. My completed code looked like this:

Please Login or Register  to view this content.

The loop limit is set to 1214 because that is, to my knowledge, the longest string of matches in the column. In case I'm wrong, I placed a RETURN() past the FOR/NEXT loop if it should run 1214 iterations and still find a matching value.

Brian