Referring to named ranges in a macro

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!

Are you talking VBA or worksheet?

Assuming VBA

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



--
HTH

Bob Phillips

"Pontificateur" <Pontificateur@discussions.microsoft.com> wrote in message
news:67FACC86-14B6-4496-9D24-DA031395B988@microsoft.com...
> 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!
>

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
Palo Alto, CA


"Pontificateur" <Pontificateur@discussions.microsoft.com> wrote in message
news:67FACC86-14B6-4496-9D24-DA031395B988@microsoft.com...
> 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!
>

> 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

--
Tim Williams
Palo Alto, CA


"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:Oo5pjskuFHA.3000@TK2MSFTNGP12.phx.gbl...
> 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
> Palo Alto, CA
>
>
> "Pontificateur" <Pontificateur@discussions.microsoft.com> wrote in message
> news:67FACC86-14B6-4496-9D24-DA031395B988@microsoft.com...
> > 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!
> >
>
>

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

"Bob Phillips" wrote:

> Are you talking VBA or worksheet?
>
> Assuming VBA
>
> lngGLCode = IIF(worksheets("RawHours").Range("B2").value=836041), _
> Range("GLCodes")(1,3), Range("GLCodes")(2,4))
>
>
>
> --
> HTH
>
> Bob Phillips
>
> "Pontificateur" <Pontificateur@discussions.microsoft.com> wrote in message
> news:67FACC86-14B6-4496-9D24-DA031395B988@microsoft.com...
> > 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!
> >
>
>
>

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

"Tim Williams" wrote:

> > 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
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Tim Williams" <timjwilliams at gmail dot com> wrote in message
> news:Oo5pjskuFHA.3000@TK2MSFTNGP12.phx.gbl...
> > 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
> > Palo Alto, CA
> >
> >
> > "Pontificateur" <Pontificateur@discussions.microsoft.com> wrote in message
> > news:67FACC86-14B6-4496-9D24-DA031395B988@microsoft.com...
> > > 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!
> > >
> >
> >
>
>
>