I'm trying to read the value of a named range from Excel into VBA code associated with a specific worksheet (Click Box), but it's just not working! The problem is that the named range on the worksheet (CountR), which is a row count for a table that can expand or contract, has the formula =count(indirect("D8:D500")). I need the indirect function because the table can grow or shrink according to the individual user's requirements and I don't want the count row range to change. The indirect function is causing a problem when I try to read the value of CountR into my VBA code via the line iCountR = [CountR].
Everything works fine if I place the =count(indirect("D8:D500")) formula in a cell and name the cell CountR. But if I simply name the range in Name Manager and reference the name from VBA as per the above code a value of zero is returned.
My work-around using a hidden helper cell works fine, but I'd like to avoid doing this if I can, since the worksheet will be used by many people and there is always the risk that fiddling with the worksheet structure may occur and the important hidden cell gets over-written or deleted!
Any ideas on how to solve this? I'm actually thinking that volatile nature of Indirect may preclude doing what I want to do.