macro

[Cindy Wang]

I try to record a macro, but have run into a problem: my macro
includes vlookup a pivot table. But the pivot table sheet did not
always come out the same name( in my macro, it is called " sheet2"),
so the vlooup does not know where to look, and returns error. Does
someone know how to solve this problem? Thanks a lot!

 

[pascal baro]

Hi Cindy,
Provide the code of the macro you are recording, you can find it in the vbaide by typing ALT+F11 and look for the workbook where you have created themacro in the left window and there you should find one or more macro in the module folder. Copy the code where you want to customize the code for your vlookup. Then someone here or me should be able to help.
Pascal Baro

 

[pascal baro]

Hi Cindy,

I have received your code from the macro you have recorded.
I can't tell much myself but first to define a name for your pivot table. This can be done in Excel in the formula tab "define name" then check in "Name manage" if you run 2007 or 2010 or the insert menu I think if you run 2003,tough it is still called up with "define name" or "name manager".

Then in your code, replace the field of the worksheet you want to query in the vlookup function with the name you have defined. I can see in your code:

ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-2],'navision '!R2C1:R8000C13,12,FALSE)"

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheet1!R5C1:R3000C3,2,FALSE)"

The reason your code doesn't find the vlookup value is when you record it, you change worksheets, at one point you apply vlookup onto another worksheet (navision) and later on from this worksheet, you apply vlookup. Moreover,I don't know if you've ticked relative reference when you've created the macro but if it's the case, if you're moving while recording your macro, running your macro can be different... And last thing which makes things not easy, your using column row notation, this is a good thing if you want to get into coding but at many levels "A1, B2..." notation is more simple.

So for now, I can just tell you to define a name for your pivot table and use that same name in the vlookup: "=VLOOKUP(Sheet1!RC[-2],NEWNAME,12,FALSE)" position yourself after the "[-2]," coma and press F3, it will bring the name you have defined automatically.

Apply this to the other vlookup formula and it should be working.

HTH,
Pascal Baro

 

[Cindy Wang]

Hi Cindy,

I have received your code from the macro you have recorded.
I can't tell much myself but first to define a name for your pivot table.This can be done in Excel in the formula tab "define name" then check in "Name manage" if you run 2007 or 2010 or the insert menu I think if you run 2003,tough it is still called up with "define name" or "name manager".

Then in your code, replace the field of the worksheet you want to query in the vlookup function with the name you have defined. I can see in your code :

 ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-2],'navision '!R2C1:R8000C13,12,FALSE)"

 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheet1!R5C1:R3000C3,2,FALSE)"

The reason your code doesn't find the vlookup value is when you record it, you change worksheets, at one point you apply vlookup onto another worksheet (navision) and later on from this worksheet, you apply vlookup. Moreover, I don't know if you've ticked relative reference when you've created themacro but if it's the case, if you're moving while recording your macro, running your macro can be different... And last thing which makes things noteasy, your using column row notation, this is a good thing if you want to get into coding but at many levels "A1, B2..." notation is more simple.

So for now, I can just tell you to define a name for your pivot table anduse that same name in the vlookup:      "=VLOOKUP(Sheet1!RC[-2],NEWNAME,12,FALSE)" position yourself after the "[-2]," coma and press F3, it will bring the name you have defined automatically.

Apply this to the other vlookup formula and it should be working.

HTH,
Pascal Baro

I try to record a macro, but have run into a problem:  my macro
includes vlookup a pivot table.  But the pivot table sheet did not
always come out the same name( in my macro, it is called " sheet2"),
so the vlooup does not know where to look, and returns error.    Does
someone know how to solve this problem?   Thanks a lot!- Hide quoted text -

- Show quoted text -

How could I define the name of the pivot table before it was
created? Is there a way just to control the name of the pivot table
using (instead of using sheet 1, sheet 2 and sheet3 randomly, just
keep using sheet1? Thanks,

 

[Cindy Wang]

Hi Cindy,

I have received your code from the macro you have recorded.
I can't tell much myself but first to define a name for your pivot table. This can be done in Excel in the formula tab "define name" then check in"Name manage" if you run 2007 or 2010 or the insert menu I think if you run 2003,tough it is still called up with "define name" or "name manager".

Then in your code, replace the field of the worksheet you want to queryin the vlookup function with the name you have defined. I can see in your code :

 ActiveCell.FormulaR1C1 = "=VLOOKUP(Sheet1!RC[-2],'navision '!R2C1:R8000C13,12,FALSE)"

 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheet1!R5C1:R3000C3,2,FALSE)"

The reason your code doesn't find the vlookup value is when you record it, you change worksheets, at one point you apply vlookup onto another worksheet (navision) and later on from this worksheet, you apply vlookup. Moreover, I don't know if you've ticked relative reference when you've created the macro but if it's the case, if you're moving while recording your macro,running your macro can be different... And last thing which makes things not easy, your using column row notation, this is a good thing if you want to get into coding but at many levels "A1, B2..." notation is more simple.

So for now, I can just tell you to define a name for your pivot table and use that same name in the vlookup:      "=VLOOKUP(Sheet1!RC[-2],NEWNAME,12,FALSE)" position yourself after the "[-2]," coma and press F3, it will bring the name you have defined automatically.

Apply this to the other vlookup formula and it should be working.

HTH,
Pascal Baro

- Show quoted text -

How could I define the name of the pivot table before it was
created?   Is there a way just to control the name of the pivot table
using (instead of using sheet 1, sheet 2 and sheet3 randomly, just
keep using sheet1?   Thanks,- Hide quoted text -

- Show quoted text -

Also could you tell me what do you mean by column row notation? I
recorded the Marco, so I don't know what you mean by that. Could you
copy the script here if you don't mind? Thanks,