+ Reply to Thread
Results 1 to 15 of 15

ISBLANK() not working with "R1C1" reference

Hybrid View

  1. #1
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,645

    Re: ISBLANK() not working with "R1C1" reference

    "R7C2" is a literal string, it's not a cell reference.

    Try losing the quotes.
    If posting code please use code tags, see here.

  2. #2
    Forum Contributor
    Join Date
    09-23-2008
    Location
    Rhode Island
    MS-Off Ver
    Excel for Mac 2011 and 2007 on PC
    Posts
    265

    Re: 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. = EMBED("Forms.ComboBox.1","") error "Reference not valid."
    By CatharinaCatharina in forum Excel General
    Replies: 2
    Last Post: 12-11-2014, 09:58 AM
  2. Using R1C1 formula in VBA changes all references from CELL("address") function to R1C1?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-20-2014, 04:17 PM
  3. [SOLVED] IF formula not working =IF(NOT(ISBLANK(M3)),"",IF(ISBLANK(L3),"",TODAY()-L3))
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2014, 02:37 PM
  4. Replies: 9
    Last Post: 11-06-2012, 08:59 AM
  5. Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)" not working
    By redders in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2011, 03:52 PM
  6. Replies: 6
    Last Post: 09-08-2011, 02:04 PM
  7. [SOLVED] Puzzled by statement about R1C1 style in "Escape from Excel Hell"
    By Bob.Stromberg@gmail.com in forum Excel General
    Replies: 3
    Last Post: 04-11-2006, 08:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1