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
– Elbert Villarreal, Commented Feb 29, 2016 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
– Maxwell, Commented Feb 29, 2016 at 22:55
The loop after the 4th iteration, will delete the formula inside N2, please re-chek your code. — Elbert Villarreal
– Elbert Villarreal, Commented Feb 29, 2016 at 22:56
Could you please help me correcting this part ActiveCell.Formula = "=" & Replace(formula_user, "x", " & N2 & ") @ElbertVillarreal — Maxwell
– Maxwell, Commented Feb 29, 2016 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 — Elbert Villarreal
– Elbert Villarreal, Commented Feb 29, 2016 at 22:59

Elbert Villarreal · Accepted Answer · 2016-02-29 22:58:06Z

0

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.

answered Feb 29, 2016 at 22:58

Elbert Villarreal

1,7261 gold badge11 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Maxwell Over a year ago

I deleted the N2 and in your ActiveCell.Offset(2,i+10).Address(False,False) THANKS!

Community · Accepted Answer · 2017-05-23 11:59:25Z

0

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

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Feb 29, 2016 at 23:17

snoopen

2251 silver badge9 bronze badges

Collectives™ on Stack Overflow

Replace Cell Address using Replace Formula in VBA Excel

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related