CODEWITHSUNDEEP
vbaexcel
ManWithManyCheeses
ManWithManyCheeses

Reputation: 13

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

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.

Upvotes: 1

Views: 819

Answers (1)

A.S.H
A.S.H

Reputation: 29352

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.

Upvotes: 3

Related Questions