Using EQUIV and INDEX fonction in vba

[Guest]

Hello,
I have a database which is like the folowing (sheet #1):
Column 1 is an index (like SSN)
Column 2 to 27 is data
I would like to make a second sheet which is linked to the first sheet using
reference formula. So, the order of the sheet#1 can change without affecting
the sheet #2. I have tried to program that but does't work well.
c = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
d = ActiveSheet.Name
Columns("B:B").Insert ' This column will contain the MATCH function
Range("B3:B100").FormulaR1C1 = "=MATCH(RC[-1]," & c & "!A:A,0) 'this
gives #NAME in excel but if I click on, excel calcul the value
Range("C3:AB100").FormulaR1C1 = "=INDEX('" & c & "'!A:AZ,'" & d &
"'!RC[1]," & column()-1 & ")"

Any idea ?
Thanks !

 

[Tom Ogilvy]

Your mixing A1 and R1C1 notation in your formulas. Since you use
FormulaR1C1, it sees A:A and A:AZ as undefined names.

 

[Guest]

Thanks!
I don't really want to use R1C1 formula. I tried this and it words good!

Do you know how to replace the column()-1 by the value. I don't want
column()-1 formula in excel. something like range.column.value
Range("B3:B100") = "=MATCH('sheet2'!A:A,'sheet1'!A:A,0)"
Range("C3:AB100") = "=INDEX('sheet1'!$A:$AA,'sheet2'!$B:$B,column()-1)"

--
Alex St-Pierre

Your mixing A1 and R1C1 notation in your formulas. Since you use
FormulaR1C1, it sees A:A and A:AZ as undefined names.

--
Regards,
Tom Ogilvy

Hello,
I have a database which is like the folowing (sheet #1):
Column 1 is an index (like SSN)
Column 2 to 27 is data
I would like to make a second sheet which is linked to the first sheet using
reference formula. So, the order of the sheet#1 can change without affecting
the sheet #2. I have tried to program that but does't work well.
c = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
d = ActiveSheet.Name
Columns("B:B").Insert ' This column will contain the MATCH function
Range("B3:B100").FormulaR1C1 = "=MATCH(RC[-1]," & c & "!A:A,0) 'this
gives #NAME in excel but if I click on, excel calcul the value
Range("C3:AB100").FormulaR1C1 = "=INDEX('" & c & "'!A:AZ,'" & d &
"'!RC[1]," & column()-1 & ")"

Any idea ?
Thanks !

 

[Guest]

From a simplest way:
Range("A1:AB1") = range("A1:AB1").Column doesn't word.. (gives 1 on each
column
should gives: 1 2 3 4 5 6 7 etc.
--
Alex St-Pierre

Thanks!
I don't really want to use R1C1 formula. I tried this and it words good!

Do you know how to replace the column()-1 by the value. I don't want
column()-1 formula in excel. something like range.column.value
Range("B3:B100") = "=MATCH('sheet2'!A:A,'sheet1'!A:A,0)"
Range("C3:AB100") = "=INDEX('sheet1'!$A:$AA,'sheet2'!$B:$B,column()-1)"

--
Alex St-Pierre

Your mixing A1 and R1C1 notation in your formulas. Since you use
FormulaR1C1, it sees A:A and A:AZ as undefined names.

--
Regards,
Tom Ogilvy

Hello,
I have a database which is like the folowing (sheet #1):
Column 1 is an index (like SSN)
Column 2 to 27 is data
I would like to make a second sheet which is linked to the first sheet using
reference formula. So, the order of the sheet#1 can change without affecting
the sheet #2. I have tried to program that but does't work well.
c = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
d = ActiveSheet.Name
Columns("B:B").Insert ' This column will contain the MATCH function
Range("B3:B100").FormulaR1C1 = "=MATCH(RC[-1]," & c & "!A:A,0) 'this
gives #NAME in excel but if I click on, excel calcul the value
Range("C3:AB100").FormulaR1C1 = "=INDEX('" & c & "'!A:AZ,'" & d &
"'!RC[1]," & column()-1 & ")"

Any idea ?
Thanks !