Referring to named ranges in a macro

[Pontificateur]

I'm trying to refer to the named range "GLCodes" in a formula, as in the
following...

lngGLCode = IIF((RawHours!B2=836041), GLCodes(1,3), GLCodes(2,4))

When this formula is placed in a cell, I want the resulting value to reflect
what is in GLCodes row 1, column 3, etc.
I know this is Excel 101, and it's frustrating to not be able to make
something this (apparently) simple work. Your help will save me hours!

 

[Bob Phillips]

Are you talking VBA or worksheet?

Assuming VBA

lngGLCode = IIF(worksheets("RawHours").Range("B2").value=836041), _
Range("GLCodes")(1,3), Range("GLCodes")(2,4))

 

[Tim Williams]

Since each of the cells in your named range seems to represent a specific
value, why not just give them each a name of their own?

Anyway - for your original approach:

lngGLCode = IIF((RawHours!B2=836041), INDEX(GLCodes,1,3),
INDEX(GLCodes,2,4))


Tim

 

[Tim Williams]

Anyway - for your original approach:

lngGLCode = IIF((RawHours!B2=836041), INDEX(GLCodes,1,3),
INDEX(GLCodes,2,4))

oops that's clearly rubbish now I come back to it....

Tim

 

[Pontificateur]

Thanks, Bob, I was indeed referring to VBA and your answer gave me exactly
what I need to get results! You are much appreciated!

 

[Pontificateur]

Thanks, anyway, Tim. Your reply caused me to investigate "INDEX" and gave me
some ideas!