Excel 2003 - VBA - Vlookup generation

[C Brandt]

Today just doesn't look like it is going to be MY Day. It seems that I am
leaning on this group heavily today, so thanks alot!

This is what I have and what I need:

Column 1 is the Key name to look up.
Column 2 is the lookup formula
Column 3 is the Table NAME (Generated from a date by =TEXT (A1,"mm-yy") )
Column 4 is the location of the data to return

This is what I would like the formula in col 2 to look like

=Vlookup($A2,06-07,11,0)

Col. 1 Col. 2 Col. 3 Col. 4
Bra065 06-07 11

I tried this in VBA:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1]," & Cells(2,3) & "," & Cells(2,4)
& ",0)"

And it generates
=Vlookup($A2,6-7,11,0)
Changes the 06-07 to 6-7

I think I am brain dead, because I cannot come up with something that works.

Thanks,
Craig

 

[Dave Peterson]

I couldn't name a table 06-07, so any suggestion I have won't work.

Are you sure that your table is really named that?

After you get that resolved, maybe this would work:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],rc[1],rc[2],0)"

If it doesn't help, try building the working formula by hand and post back with
that formula.

Today just doesn't look like it is going to be MY Day. It seems that I am
leaning on this group heavily today, so thanks alot!

This is what I have and what I need:

Column 1 is the Key name to look up.
Column 2 is the lookup formula
Column 3 is the Table NAME (Generated from a date by =TEXT (A1,"mm-yy") )
Column 4 is the location of the data to return

This is what I would like the formula in col 2 to look like

=Vlookup($A2,06-07,11,0)

Col. 1 Col. 2 Col. 3 Col. 4
Bra065 06-07 11

I tried this in VBA:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1]," & Cells(2,3) & "," & Cells(2,4)
& ",0)"

And it generates
=Vlookup($A2,6-7,11,0)
Changes the 06-07 to 6-7

I think I am brain dead, because I cannot come up with something that works.

Thanks,
Craig

 

[C Brandt]

Dave:
Thanks for the input. Yes, I realized that the NAME I used would not work,
so I changed that, but I still have the issue of inserting this NAME into
the formula.
Like I said, "Very bad day yesterday".
As soon as I saw your note I had a DUH! moment. The fix was fairly clear
this morning. I changed the name and to bring in the name from the cell, I
used Indirect.
The final formula in my example would look like this:
Col. 1 Col. 2 Col. 3 Col. 4
Bra065 WS0807 11

Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],Indirect(rc[1],false),rc[2],0)"

This appears to work as needed.

Thanks for you assist,
Craig

I couldn't name a table 06-07, so any suggestion I have won't work.

Are you sure that your table is really named that?

After you get that resolved, maybe this would work:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],rc[1],rc[2],0)"

If it doesn't help, try building the working formula by hand and post back with
that formula.

Today just doesn't look like it is going to be MY Day. It seems that I am
leaning on this group heavily today, so thanks alot!

This is what I have and what I need:

Column 1 is the Key name to look up.
Column 2 is the lookup formula
Column 3 is the Table NAME (Generated from a date by =TEXT (A1,"mm-yy") )
Column 4 is the location of the data to return

This is what I would like the formula in col 2 to look like

=Vlookup($A2,06-07,11,0)

Col. 1 Col. 2 Col. 3 Col. 4
Bra065 06-07 11

I tried this in VBA:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1]," & Cells(2,3) & "," & Cells(2,4)
& ",0)"

And it generates
=Vlookup($A2,6-7,11,0)
Changes the 06-07 to 6-7

I think I am brain dead, because I cannot come up with something that works.

Thanks,
Craig

 

[Dave Peterson]

Yep.

And it's good you caught that indirect() function, too!

Dave:
Thanks for the input. Yes, I realized that the NAME I used would not work,
so I changed that, but I still have the issue of inserting this NAME into
the formula.
Like I said, "Very bad day yesterday".
As soon as I saw your note I had a DUH! moment. The fix was fairly clear
this morning. I changed the name and to bring in the name from the cell, I
used Indirect.
The final formula in my example would look like this:
Col. 1 Col. 2 Col. 3 Col. 4
Bra065 WS0807 11

Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],Indirect(rc[1],false),rc[2],0)"

This appears to work as needed.

Thanks for you assist,
Craig

I couldn't name a table 06-07, so any suggestion I have won't work.

Are you sure that your table is really named that?

After you get that resolved, maybe this would work:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],rc[1],rc[2],0)"

If it doesn't help, try building the working formula by hand and post back with
that formula.

Today just doesn't look like it is going to be MY Day. It seems that I am
leaning on this group heavily today, so thanks alot!

This is what I have and what I need:

Column 1 is the Key name to look up.
Column 2 is the lookup formula
Column 3 is the Table NAME (Generated from a date by =TEXT (A1,"mm-yy") )
Column 4 is the location of the data to return

This is what I would like the formula in col 2 to look like

=Vlookup($A2,06-07,11,0)

Col. 1 Col. 2 Col. 3 Col. 4
Bra065 06-07 11

I tried this in VBA:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1]," & Cells(2,3) & "," & Cells(2,4)
& ",0)"

And it generates
=Vlookup($A2,6-7,11,0)
Changes the 06-07 to 6-7

I think I am brain dead, because I cannot come up with something that works.

Thanks,
Craig

 

[Dave Peterson]

ps. If you're putting a name in that cell, you don't need the second parm
(false) in that =indirect() portion.

If you were putting an address in R1C1 reference style, then you'd want that
piece.

Yep.

And it's good you caught that indirect() function, too!

Dave:
Thanks for the input. Yes, I realized that the NAME I used would not work,
so I changed that, but I still have the issue of inserting this NAME into
the formula.
Like I said, "Very bad day yesterday".
As soon as I saw your note I had a DUH! moment. The fix was fairly clear
this morning. I changed the name and to bring in the name from the cell, I
used Indirect.
The final formula in my example would look like this:
Col. 1 Col. 2 Col. 3 Col. 4
Bra065 WS0807 11

Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],Indirect(rc[1],false),rc[2],0)"

This appears to work as needed.

Thanks for you assist,
Craig

I couldn't name a table 06-07, so any suggestion I have won't work.

Are you sure that your table is really named that?

After you get that resolved, maybe this would work:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1],rc[1],rc[2],0)"

If it doesn't help, try building the working formula by hand and post back with
that formula.



C Brandt wrote:

Today just doesn't look like it is going to be MY Day. It seems that I am
leaning on this group heavily today, so thanks alot!

This is what I have and what I need:

Column 1 is the Key name to look up.
Column 2 is the lookup formula
Column 3 is the Table NAME (Generated from a date by =TEXT (A1,"mm-yy") )
Column 4 is the location of the data to return

This is what I would like the formula in col 2 to look like

=Vlookup($A2,06-07,11,0)

Col. 1 Col. 2 Col. 3 Col. 4
Bra065 06-07 11

I tried this in VBA:
Cells(2,2).formulaR1C1 = "=Vlookup(RC[-1]," & Cells(2,3) & "," & Cells(2,4)
& ",0)"

And it generates
=Vlookup($A2,6-7,11,0)
Changes the 06-07 to 6-7

I think I am brain dead, because I cannot come up with something that works.

Thanks,
Craig