For x con letras en vez de números

enrux · el 16 de julio

Hola compañeros una ayuda

Tengo las Siguientes Formulas en codigo:



Set h1 = Sheets("Hoja1")

j = "=SUMPRODUCT(--((R2C[-8]:RC[-8]&R2C[-7]:RC[-7]&R2C[-6]:RC[-6]&R2C[-5]:RC[-5]&R2C[-4]:RC[-4]&R2C[-3]:RC[-3]&R2C[-2]:RC[-2])=(RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2])))"


k = "=IFERROR(INDEX(INDIRECT(Range!R9C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-8],IF(INDIRECT(Range!R4C2)=RC[-7],IF(INDIRECT(Range!R2C2)=RC[-9],IF(INDIRECT(Range!R6C2)=RC[-5],IF(INDIRECT(Range!R7C2)=RC[-4],IF(INDIRECT(Range!R5C2)=RC[-6],IF(INDIRECT(Range!R8C2)=(VLOOKUP(RC[-3],Criterios!R1C11:R13C12,2,0)),ROW(INDIRECT(Range!R9C2))))))))),RC[-1])),"""")"


l = "=IFERROR(INDEX(INDIRECT(Range!R10C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-9],IF(INDIRECT(Range!R4C2)=RC[-8],IF(INDIRECT(Range!R2C2)=RC[-10],IF(INDIRECT(Range!R6C2)=RC[-6],IF(INDIRECT(Range!R7C2)=RC[-5],IF(INDIRECT(Range!R5C2)=RC[-7],IF(INDIRECT(Range!R8C2)=(VLOOKUP(RC[-4],Criterios!R1C11:R13C12,2,0)),ROW(INDIRECT(Range!R10C2))))))))),RC[-2])),"""")"


M = "=IF(RC[-5]=11050503,IFERROR(INDEX(INDIRECT(Range!R8C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-10],IF(INDIRECT(Range!R4C2)=RC[-9],IF(INDIRECT(Range!R2C2)=RC[-11],IF(INDIRECT(Range!R6C2)=RC[-7],IF(INDIRECT(Range!R7C2)=RC[-6],IF(INDIRECT(Range!R5C2)=RC[-8],ROW(INDIRECT(Range!R8C2)))))))),RC[-3])),""""),"""")"


N = "=IF(RC[-6]=11100542,11100543,RC[-6])"


O = "=IF(RC[-7]=11050503,IFERROR(IF(SEARCH(""TVS"",RC[-2]),900260544,""""),860006797),"""")"


P = "=VLOOKUP(RC[-8],Criterios!C[-7]:C[-6],2,0)"


Q = "=IF(RC[-7]=11050503,IFERROR(IF(SEARCH(""TVS"",RC[-2]),900260544,""""),860006797),"""")"


R = "=IF(RC[-6]=11100542,11100543,RC[-6])"


S = "=VLOOKUP(RC[-8],Criterios!C[-7]:C[-6],2,0)"


T = "=IF(RC[-8]=""D"",RC[-12],0)"


U = "=IF(RC[-9]=""C"",RC[-13],0)"


V = "=VLOOKUP(RC[-11],Criterios!C[-12]:C[-11],2,0)"


h1.Range("j2").FormulaArray = j[/CODE]

Lo que busco que digamos Variable X se remplaze por la formula y la Fila es decir

h1.Range(X&"2").FormulaArray = X

es decir que me pegue la formula en en rango J1 la formula J

Con la Condicion que si es O P Q sea FormulaArray o de lo contrario FormulaR1C1

Lo que busca es saber si se puede hacer de la forma como la acabo de plantear..

Gracias

Antoni · el 17 de julio

Pudiendo asignar la fórmula directamente a la celda, no se para que quieres complicarte la vida, en fin, tu sabrás el porqué.

Dim R(10), A(3), lR, lA


lR = "JKLMNRSTUV"

R(1) = "=SUMPRODUCT(--((R2C[-8]:RC[-8]&R2C[-7]:RC[-7]&R2C[-6]:RC[-6]&R2C[-5]:RC[-5]&R2C[-4]:RC[-4]&R2C[-3]:RC[-3]&R2C[-2]:RC[-2])=(RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2])))"

R(2) = "=IFERROR(INDEX(INDIRECT(Range!R9C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-8],IF(INDIRECT(Range!R4C2)=RC[-7],IF(INDIRECT(Range!R2C2)=RC[-9],IF(INDIRECT(Range!R6C2)=RC[-5],IF(INDIRECT(Range!R7C2)=RC[-4],IF(INDIRECT(Range!R5C2)=RC[-6],IF(INDIRECT(Range!R8C2)=(VLOOKUP(RC[-3],Criterios!R1C11:R13C12,2,0)),ROW(INDIRECT(Range!R9C2))))))))),RC[-1])),"""")"

R(3) = "=IFERROR(INDEX(INDIRECT(Range!R10C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-9],IF(INDIRECT(Range!R4C2)=RC[-8],IF(INDIRECT(Range!R2C2)=RC[-10],IF(INDIRECT(Range!R6C2)=RC[-6],IF(INDIRECT(Range!R7C2)=RC[-5],IF(INDIRECT(Range!R5C2)=RC[-7],IF(INDIRECT(Range!R8C2)=(VLOOKUP(RC[-4],Criterios!R1C11:R13C12,2,0)),ROW(INDIRECT(Range!R10C2))))))))),RC[-2])),"""")"

R(4) = "=IF(RC[-5]=11050503,IFERROR(INDEX(INDIRECT(Range!R8C2),SMALL(IF(INDIRECT(Range!R3C2)=RC[-10],IF(INDIRECT(Range!R4C2)=RC[-9],IF(INDIRECT(Range!R2C2)=RC[-11],IF(INDIRECT(Range!R6C2)=RC[-7],IF(INDIRECT(Range!R7C2)=RC[-6],IF(INDIRECT(Range!R5C2)=RC[-8],ROW(INDIRECT(Range!R8C2)))))))),RC[-3])),""""),"""")"

R(5) = "=IF(RC[-6]=11100542,11100543,RC[-6])"

R(6) = "=IF(RC[-6]=11100542,11100543,RC[-6])"

R(7) = "=VLOOKUP(RC[-8],Criterios!C[-7]:C[-6],2,0)"

R(8) = "=IF(RC[-8]=""D"",RC[-12],0)"

R(8) = "=IF(RC[-9]=""C"",RC[-13],0)"

R(10) = "=VLOOKUP(RC[-11],Criterios!C[-12]:C[-11],2,0)"

For x = 1 To 10: Range(Mid(lR, x, 1) & 2).FormulaR1C1 = R(x): Next


lA = "OPQ"

A(1) = "=IF(RC[-7]=11050503,IFERROR(IF(SEARCH(""TVS"",RC[-2]),900260544,""""),860006797),"""")"

A(2) = "=VLOOKUP(RC[-8],Criterios!C[-7]:C[-6],2,0)"

A(3) = "=IF(RC[-7]=11050503,IFERROR(IF(SEARCH(""TVS"",RC[-2]),900260544,""""),860006797),"""")"

For x = 1 To 3: Range(Mid(lR, x, 1) & 2).FormulaArray = A(x): Next

[/CODE]