Using named ranges

Looking to doa few things with a macro to process info. At the moment I am simply referring to the worksheets but I'd rather be able to refer to the named ranges. These are not sheet specific but acorss the whole book.

My problem is just simply testing it I'm clearly getting something wrong. SO I have the following line of code:

MsgBox ThisWorkbook.Names("ated_1").Cells(1, 1).Value

Now in my head that should show me the contents of the top left cell of the named range "ated_1". AT the moment it just gives an error 1004 - application-defined ot object-defined error. I am sure to someone that knows about these things it is blindingly obvious but what am I getting wrong?

And yes - ated_1 does exist

TIA

Try

MsgBox Range("ated_1").Cells(1, 1).Value

Thanks to both of you. I was under the impression that using Range would limit it to only the worksheet I was on. Clearly wrong. All sorted ta.

You are welcome and thanks for the rep

Glad we could help & thanks for the feedback.

I was under the impression that using Range would limit it to only the worksheet I was on

It will if the named range is sheet scope, but not if it's workbook scope.

Also tried columns.count but it, predicatably, gives me the number of columns, not the number of items across a row.

I'm going to take advantage of your collective good nature now

Having selected a range and performed most of what I want to do with it, the one thing I'm having a nightmare with is using COUNTA to see how many entries in a particular row of the range.

I've tried all of the following (recreated so there may be typos)

tbl_flag = WorksheetFunction.CountA(Range(ws_needed).Rows(info_loop))
tbl_flag = WorksheetFunction.CountA(Range(ws_needed).cells(info_loop,2),cells(info_loop,20))
tbl_flag = WorksheetFunction.CountA(Range(ws_needed).range(cells(info_loop,2),cells(info_loop,20)))

For the first row it fails on there are two columns only but it will only return 1. What am I missing here?

This works for me

MsgBox WorksheetFunction.CountA(Range("fluff").Rows(1))

Originally Posted by Fluff13

This works for me

MsgBox WorksheetFunction.CountA(Range("fluff").Rows(1))

Thanks for that. I'll factor that back in

What does "info_loop" and "ws_needed" hold?

Also, typically speaking, you are better off using Application.CountA or Evaluate() rather than WorksheetFunction.

Try following:

Application.CountA(Range(ws_needed))

Or

Application.CountA(Range(cells(info_loop,2),cells(info_loop,20)))

info_loop = number
ws_needed = named range.

DIdn't even know application.counta existed. Had always been told to use the worksheet function - every day's a school day!

Handy. Thanks very much for that