hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.

<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code

I might be missing something here, but why VBA code? Won't just an ordinary
VLOOKUP do what you want, especially as it's looking up a sheet in the same
workbook?

I am trying to automate the whole process so that
at the press of a button, the data processing and analysis can be done .

yes, it works if it is just a normal formula in a cell.
but in VBA, i have problems referencing it .


"Gordon" wrote in message
...
<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code

I might be missing something here, but why VBA code? Won't just an
ordinary VLOOKUP do what you want, especially as it's looking up a sheet
in the same workbook?

Hi,

I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5]) with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign whereas it should be on both sides of the sheet name (actually with this name they aren't necessary at all as there are no spaces in it). Besides you are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=". Try this:

Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2, 2,0)"

Regards,
KL



<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.

<a wrote in message ...
I am trying to automate the whole process so that
at the press of a button, the data processing and analysis can be done .

But you are referencing a sheet in the same workbook, so when you open the
workbook, the VLOOKUP will automatically update! So I ask again, am I
missing something? Why the VBA?

....actually, I would do this:

Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable!R2C1:R38C2, 2,0)"

Regards,
KL


"KL" wrote in message ...
Hi,

I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5]) with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign whereas it should be on both sides of the sheet name (actually with this name they aren't necessary at all as there are no spaces in it). Besides you are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=". Try this:

Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2, 2,0)"

Regards,
KL



<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet 1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.

thanks alot.

since i am at cell M2 on sheet 1,
the code : lookuptable!R[-3]C1:R[33]C2 is taking pivot from which cell?
should i take reference from A1 since this is on sheet 2.

also, i am going to copy this whole formula down the column, will R1C1
formula automatically shift the referencing for me?
i.e. if M2 calculates from cell H2, M3 will calculate from cell H3.

thanks again.

"KL" wrote in message
...
Hi,

I guess it is clearly beacuse you are mixing the R1C1 notation (RC[-5])
with A1 notation ($A2:$B38) in one formula. Also you use only one '-sign
whereas it should be on both sides of the sheet name (actually with this
name they aren't necessary at all as there are no spaces in it). Besides you
are explicitly saying it is a R1C1 formula by "Range("M2").FormulaR1C1=".
Try this:

Range("M2").FormulaR1C1="=VLOOKUP(RC[-5], lookuptable!R[-3]C1:R[33]C2,
2,0)"

Regards,
KL



<a wrote in message ...
hi,

I have 2 sheets in my workbook,

sheet 1 contains the main analysis of my data

sheet 2 contains a table which I will reference with VLOOKUP from sheet
1


I have written the following VBA code for my purpose:
Worksheets(1).Activate //activate sheet 1
Range("M2").FormulaR1C1 = "=VLOOKUP(RC[-5], lookuptable'!$A2:$B38, 2,
false)" //input into cell M2 of sheet 1 this formula.

when I run my code,
cell M2 in sheet 1 will give a #NAME? error and when I look at the
formula
in the cell:
=VLOOKUP(H2,personal.xls!'A2':'B38',2,FALSE)

notice the extra single quotes around A2 and B38.

ps: i defined my module in personal.xls to be able to access it from any
workbook.

thnks.
Michael.

Hi,

since i am at cell M2 on sheet 1,
the code : lookuptable!R[-3]C1:R[33]C2 is taking pivot from which cell? should i take reference from A1 since this is on sheet 2.

I am not sure what you mean by this :-(
The reference to table should be lookuptable!R2C1:R38C2 if you want $A$1:$B$38

also, i am going to copy this whole formula down the column, will R1C1 formula automatically shift the referencing for me?
i.e. if M2 calculates from cell H2, M3 will calculate from cell H3.

Yes, it will.

Regards,
KL