Passing value of named range ... not working? should be simple no?

Hi

This is driving me nuts. I have a spreadsheet with input values on one sheet. Depending on the value chosen, it will hide/unhide rows on another sheet.

To be "more efficient" I have tried to name ranges in Excel for the cells that hold the input values (because I use this as a template and if I need to change things slightly I don't have to go back into the code to try and find all the references for the input values... so it was supposed to save time... )

But for whatever reason, I can't seem to get the code to even pass the value... tried setting it as Dim i as Range, or Dim i as Interger and neither worked.

I'm sure it's something very small I'm doing wrong.

Original Code (stating exact cell address) - works fine, I just want to change the "Cells(14,3)" for example to the named cell "Life"... Cells(17, 3) is "LTD".....

Please Login or Register  to view this content.

I get Object Variable or With Block variable not set if I just change to

Please Login or Register  to view this content.

I've tried searching internet etc, can't find similar code... any simple ideas?

Hi trillium,

The problem here is the range you are referring to is not be a single cell - e.g. based on your feedback, "Life" appears to be more than 1 cell - Thus trying to return the value of multiple cells to a single variable gives you an error. If you want to select multiple cells and refer to a single value, they all must be merged together.

Hi there, just lost my response!

Yes when I select C14 is shows Life in the name range box... and it is "supposed" to be just assinged to 1 cell.. ? I will double check in the name manager to be sure it didn't select >1 cell. Thanks for all your suggestions!