Replace Cell Address using Replace Formula in VBA Excel

Question

I want replace the x input from the user to the Cell Address value that is stored in N2. For example Cells(2, i+10) is J2 Cell, in the first loop in cell A2 it will be =J2^2 if the user wrote x^2 and the next loop would be B2=K^3 if the user wrote x^3 and so on.

    numero_formulas = InputBox("¿Cuántas fórmulas vas a ingresar?")
    Sheets("ResultadosContinua").range("I6") = numero_formulas

    For i = 1 To numero_formulas

    N2 = Cells(2, i + 10)

    formula_user = InputBox("Escribe la fórmula:" & i & "")
    Cells(2, i).Select
    Sheets("ResultadosContinua").Select
    ActiveCell.Formula = "=" & Replace(formula_user, "x", " & N2 & ")

What are you trying to do with this line `ActiveCell.Formula = "=" & Replace(formula_user, "x", " & N2 & ")`? It is the same as: `ActiveCell.Formula = "=" & Replace(formula_user, "x", n2)` — Elbert Villarreal, Feb 29 '16 at 22:51
I'm telling that in ActiveCell.Formula= the cell reference that is stored in N2 in the loop. For example in the first loop I want N2=K2 (K2 is Cells(2, i+10, considering i is 1). The user will write a math formula like x^2+2 and that line will replace x with the cell reference. — Maxwell, Feb 29 '16 at 22:55
The loop after the 4th iteration, will delete the formula inside `N2`, please re-chek your code. — Elbert Villarreal, Feb 29 '16 at 22:56
Could you please help me correcting this part ActiveCell.Formula = "=" & Replace(formula_user, "x", " & N2 & ") @ElbertVillarreal — Maxwell, Feb 29 '16 at 22:59
Please re-chek your question: `to the Cell Address value that is stored in N2`. And please read this [How to ask](http://stackoverflow.com/help/how-to-ask) — Elbert Villarreal, Feb 29 '16 at 22:59

score 0 · Accepted Answer · answered Feb 29 '16 at 22:58

If you want the address of the N2, (4 columns away from the activecell) you need you use this:

numero_formulas = InputBox("¿Cuántas fórmulas vas a ingresar?")
Sheets("ResultadosContinua").Range("I6") = numero_formulas

For i = 1 To numero_formulas

n2 = Cells(2, i + 10)
formula_user = InputBox("Escribe la fórmula:" & i & "")
Cells(2, i).Select
Sheets("ResultadosContinua").Select
ActiveCell.Formula = "=" & Replace(formula_user, "x", ActiveCell.Offset(0, 4).Address(False, False))

But as I said, you code will delete the formula inside the cel N2, after the 4th iteration.

I deleted the N2 and in your ActiveCell.Offset(2,i+10).Address(False,False) THANKS! — Maxwell, Feb 29 '16 at 23:02

score 0 · Answer 2 · edited May 23 '17 at 11:59

I'd suggest reading up on how to avoid using .Select as per this question. I would also encourage using the full name of the object you're targeting. ie Cells(2,10) = "Text" will work but it's always better to write Cells(2,10).Value = "Text". Your code could then be modified to read:

Dim rngNumeroRange as Range
Dim intNumeroFormulas as Integer
Dim rngN2 as Range

Set rngNumeroRange = Sheets("ResultadosContinua").Range("I6")
intNumeroFormulas = InputBox("¿Cuántas fórmulas vas a ingresar?")
rngNumeroRange.Value = intNumeroFormulas 

For i = 1 To intNumeroFormulas 

    set rngN2 = Sheets("ResultadosContinua").Cells(2, i)

    formula_user = InputBox("Escribe la fórmula:" & i & "")
    rngN2.Formula = "=" & Replace(formula_user, "x", rngN2.Offset(0, 9).Address(False, False))

Next

Replace Cell Address using Replace Formula in VBA Excel

2 Answers2