VBA Excel How to replace a part of a value with another Cells value

Question

I want to have a cell where someone can enter a formula and then use a macro to automatically replace the words in that formula with the correct numbers which are in the same sheet.

Example:
The Sheet contains a cell(L8) with the value for Weight, lets say its 10 Another cell(L10) has the value for Height, lets say 20.

Now if someone types something like this in cell(I27): (Height+120)/Weight I want the macro to replace the text with their corresponding values so I can use that text as a formula and show the result in cell I28.

So cell(I27) would show 20+120/10 and cell(I28) would output the result of that formula.

The Values are in Column L8-L14.

I tried going with a solution I found which is the following:

With Range("I27")
    .Replace What:="Weight", _
            Replacement:="", _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            MatchCase:=False, _
            SearchFormat:=False, _
            ReplaceFormat:=False
End With

I would need to somehow tell it to get the replacement from another cell.

I thought if I could get that code working I would be able to just copy it for each word I want to replace (4) and then work on a solution to use the formula to output the result in the cell next to it.

Why replace the words? Why not just give L8 the name "Weight" and L10 the name "Height". Then a formula of =(Height+120)/Weight is valid and calculates the answer you want. — YowE3K
– YowE3K, Commented Apr 17, 2017 at 18:33
I´m not sure if I understand you correctly. I need to somehow tell excel that when someone enters those specific words in that cell that they are actually meant to be values found in another place. I just want to bypass having to manually find and type those values as the formula doesnt stay the same and you would end up having to do it pretty often. Also those values are getting calculated so they change all the time. The solution of dot.Py helped me already in that I can replace it now, all I got to do now is take that "converted" cell and use it in another cell as formula. — ManWithManyCheeses
– ManWithManyCheeses, Commented Apr 17, 2017 at 18:42
Is this for some sort of exam situation where you ask something like "How would you calculate such-and-such" and the person enters their "answer" as a "formula" and then you are running the macro to generate the result of their answer? Or are you just trying to allow the user to use the names "Weight" and "Height" (etc) instead of "L8" and "L10" (etc) in their formulas so that their formulas make more "sense"? If it is the latter, just name the cells. (Even if it is the former, you could name the cells and then just calculate I28 as Range("I28").Formula = "=" & Range("I27").Text) — YowE3K
– YowE3K, Commented Apr 17, 2017 at 18:47
What @YowE3K meant is to use Excel Named Ranges. Indeed what you are seeking is exactly what named ranges are for. — A.S.H
– A.S.H, Commented Apr 17, 2017 at 18:49
@A.S.H - I'm glad you are here to translate for me! :) (My biggest problem is trying to get people to understand what I am talking about :( That's why I like computers - they know what I mean.) — YowE3K
– YowE3K, Commented Apr 17, 2017 at 18:52

A.S.H · Accepted Answer · 2017-04-17 19:10:44Z

3

To use named ranges,

1- Select the menu Formulas --> Name Manager
2- New...--> Name: Weight, RefertTo: =Sheet1!$L$8 -->OK
3- New...--> Name: Height, RefertTo: =Sheet1!$L$10 -->OK

Now your cell L8 has the Name "Weight" and you cell L10 has the Name "Height". You can type this formula in any cell:

 =(Height+120)/Weight

And you get the resulting value of (L10+120)/L8.

p.s. in steps 2 and 3, you can do it even easier by placing the cursor in the RefersTo box and then clicking on the corresponding cell that gets the name.

edited Apr 17, 2017 at 19:10

answered Apr 17, 2017 at 19:00

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

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

5 Comments

ManWithManyCheeses Over a year ago

One little follow up question, @YowE3K posted this ´Range("I28").Formula = "=" & Range("I27").Text´ Would it be possible to squeeze the round method into it?

A.S.H Over a year ago

You mean you dont want the user to type the "=" part of the formula?

ManWithManyCheeses Over a year ago

yea, as silly as it seems. Everything is working, just trying to round the result and I would be all set.

YowE3K Over a year ago

A.S.H - You can also set a range name by just being in the cell and typing the name directly into the area to the left of the formula area. No need to go into Name Manager. @ManWithManyCheeses - if you wanted the user to type the formula without the =, and then evaluate the formula with a macro, you could use Range("I28").Formula = "=ROUND(" & Range("I27").Text & ", 0)" or Range("I28").Value = Evaluate("ROUND(" & Range("I27").Text & ", 0)")

A.S.H Over a year ago

@YowE3K lol I used to work with the formula bar hidden, to win some space on the screen. I guess many people do the same :D

Collectives™ on Stack Overflow

VBA Excel How to replace a part of a value with another Cells value

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related