Using Defined Name/Formula result in VBA

[CheshireCat]

I have an Excel spreadsheet with several columns of data. Since the columns might be re-arranged in the future, I have defined several "Named" ranges with formulas to give me the column numbers of the data.

e.g. The defined name "colFullName" refers to the formula:

Please Login or Register  to view this content.

I want to use the resulting values of the defined names/formulas in VBA, but I can't find the proper syntax to do so.

Here's the basic test I've been trying:

Please Login or Register  to view this content.

I've also tried:

Please Login or Register  to view this content.

but it just returns an error "Type mismatch (Error 13)"

What is the correct syntax to use the resulting value of a named formula in VBA?

[Leith Ross]

Hello CheshireCat,

The VBA syntax is...

Please Login or Register  to view this content.

[CheshireCat]

Hi Leith,

Sorry, that gives me a Run-Time Error '1004' Application-defined or object-defined error.

Does "Range" work when there is a formula in the defined name?

[CheshireCat]

Okay, I'll modify my approach to use a cell reference instead of a formula in the defined name. This will allow Range to work.

Thanks Leith.

[Leith Ross]

Hello ChesireCat,

Make your named range dynamic and the VBA code will work. Here is formula for the named range "colFullName".

=OFFSET(StaffList!D1,1,0,COUNTA(StaffList!D:D)-1,1)