FormulaR1C1 con variable da error 1004 error definido por la aplicación o el objeto
La línea de código que me da error es la siguiente:
ActiveCell.FormulaR1C1 = _ "=QUARTILE(R2C[-46]:R1048576C[-46],1)-" & x & "*((QUARTILE(R2C[-46]:R1048576C[-46],3)-(QUARTILE(R2C[-46]:R1048576C[-46],1))))"
No he dado con lograr que me corra la macro con la variable x
la macro completa va así
sub quartil ()
Dim contador As Double
Dim k As Double
Dim m As Double
x = 1.5
y = 1.5
Columns("AX:AX").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("AX1").Select
Range("AX1").Select
ActiveCell.FormulaR1C1 = "CUARTIL1"
Cells(1, 51).Select
ActiveCell.FormulaR1C1 = _
"=QUARTILE(R2C[-46]:R1048576C[-46],1)-" & x & "*((QUARTILE(R2C[-46]:R1048576C[-46],3)-(QUARTILE(R2C[-46]:R1048576C[-46],1))))"
Range("AY1").Select
Selection.Copy
Range("AY1:CO1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AX2").Select
ActiveCell.FormulaR1C1 = "CARTIL2"
Range("AY2").Select
ActiveCell.FormulaR1C1 = _
"=QUARTILE(R2C[-46]:R1048576C[-46],3)+" & x & "*((QUARTILE(R2C[-46]:R1048576C[-46],3)-(QUARTILE(R2C[-46]:R1048576C[-46],1))))"
Range("AY2").Select
Selection.Copy
Range("AY2:CO2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("CU2").Select
Range("AY3").Select
ActiveCell.FormulaR1C1 = "=R[-2]C[-46]"
Range("AY3").Select
Selection.Copy
Range("AY3:CO3").Select
ActiveSheet.Paste
Range("AY4").Select
ActiveCell.Value = "=IF(R[-2]C[-46]="""","""",IF(AND(R[-2]C[-46]>=R1C,R[-2]C[-46]<=R2C),R[-2]C[-46],""""))"
''''''
Range("AY4").Select
Selection.AutoFill Destination:=Range("AY4:CO4"), Type:=xlFillDefault
Range("AY4:CO4").Select
Selection.AutoFill Destination:=Range("AY4:CO406"), Type:=xlFillDefault
end sub
Respuesta de Elsa Matilde
2
y si quisietra que la variable fuera una celda o un rango? algo como
X=Cells(2,7)
Si en la celda tenés un valor como: 1,5 entonces intentalo así:
x = Str(Cells(2,7))
Sdos!
