ISBLANK() not working with "R1C1" reference

that only works if i change reference style in the preferences but that screws up my ability to make calls like this

=SELECT("R"&rowcount&"C1")

where rowcount is a variable named in a FOR/NEXT loop/ can't even remember where i was first introduced to this syntax but i've always written in A1 reference mode using quoted references when i want to make R1C1 calls. So this call selects the cell i have in mind which is the empty first cell in the row i intend to interrogate. i just use the selection with a WAIT command in the macro so i can follow along and debug FOR/NEXT problems. So the quoted reference works fine for the SELECT command. But that very same syntax won't work with ISBLANK. e.g.:

=ISBLANK("R"&rowcount&"C3")

it doesn't work regardless of which style reference i have selected in preferences. and the quoted selection code above works fine even when operating with A1 reference selected (which is to say R1C1 is not checked).

I am still mystified about the background automatic naming that makes something like that selection function work (I would call it variable definition) but whatever happens during the running of the macro does not appear to outlast the macro because when i go to define names after i've run it there is no rowcount, but obviously there is one during the running of macro as it doesn't throw errors and selects the correct cell using that concatentation with the variable rowcount. (like other names, it is quoted when first called out, but after that you don't use quotes).

I came up with a workaround for my conditional test, since i can't get that R1C1 concatenated reference to work with the ISBLANK command I simply select the cell i want to query and then use ACTIVE.CELL() as the reference for ISBLANK and then I use an OFFSET function to write the result of the function into blank space.

MIDDLE_OOBLECK
=FOR("rowcount",5,11,1)
=SELECT("R"&rowcount&"C3")
=IF(ISBLANK(ACTIVE.CELL()),FORMULA.FILL(ISBLANK(ACTIVE.CELL()),OFFSET(ACTIVE.CELL(),0,-2)),FORMULA.FILL(ISBLANK(ACTIVE.CELL()),OFFSET(ACTIVE.CELL(),0,6)))
=WAIT(NOW()+0.000015)
=NEXT()
=RETUN()

but i would still love to know why that reference works fine for SELECT but not for ISBLANK

thanks,

brian

that is an XLM macro on a macro sheet in the active workbook.

there is a CELL function that can return various aspects of cell info. takes the form =CELL("type of info", reference).

but my problem is i need to be able to make a moving numerical reference to a cell that draws the row# from a variable in a for next loop. maybe if the CELL function would take my concatenated R1C1 reference I could then ISBLANK that return. sometimes workaround is the only thing that works although using the SELECT function which will accept a concatenated R1C1 reference has got me moving for now.

I'll meditate on whether the CELL function could do better.

thanks,

brian

Tony,

thanks. i had forgotten about INDIRECT. I did a workaround but I think this would have worked.

do you know of any reference that explains which functions can actually take the quoted "R1C1" reference.

a good explanation of what circumstances excel would recognize those quotes as enclosing a reference rather than a text value would be helpful. in hind sight, i wonder why they couldn't have use scare quotes,e.g. 'R1C1' to denote that as a reference. i guess maybe too many possessives still around or something.

but thanks much for the idea.

brian

right but some other XLM macro functions can do it. For instance SELECT as you see above in the code I included above. its short so i'll repeat for simplicity.

MIDDLE_OOBLECK
=FOR("rowcount",5,11,1)
=SELECT("R"&rowcount&"C3")
=WAIT(NOW()+000015)
=NEXT()
=RETUN()

this runs like we the people and SELECT works perfectly with these mixed R1C1 references that start as quoted references and then drop out to include variables and then back into quotes. i have to think about what other functions work this way. I really wish their were a place for those of us still using XLM to knock this stuff around. I just like it better. I thing VBA is more powerful in some ways and when i hit the wall on something i have to do i record a macro and then see what the code looks like but i really don't feel capable of writing in it. just snipping and editing. but i really think the power of VBA comes from the community of folks who use it, not from the language itself. if there were a focused group of XLM folks we'd do OK. most things you can do with VBA can be done with XLM and its usually more concise.

tony,

thanks much. i do have the microsoft "macrofun" documentation. and by bible is a nice 2nd eiditon of Excel in Business by Douglas Cobb and Allan McGuffey from 1989.
the thing about the XLM macro language that really clicked for me is that it builds off of the skills you develop writing cell formulas.

and it has a precise logic. syntax of course to be conquered. and it bugs the hell out of me that excel won't let me enter an incorrect formula in a macro - i get why you can't enter it in a workbork cell, because it runs right away. but in a complicated formula ( i could spend more time writing my IFs vertically, etc.) my eyes blur over or i need to go look at something else to fix the formula and i can't get out of that cell i'm writing in. instead i end up taking out the equal sign and then sometime later i spend a half hour trying to get the macro to run once i fixed the formula because i forgot to put the equal sign back in. you'd think it would be obvious but it happens over and over. and they could have better graphic debugging worked out for non-matching parentheses and or they tell you a function has too many arguments, but there are 4 functions in the cell and they don't highlight which one.

unfortunately this will never get improved since they have abandoned it, but there is an upside to that, because there is nothing i hate more than an 'updated' version of a program i'm really comfortable with.

thanks again for passing on info.

brian