Extracting parts of formula and store in variables

Mufflon

New Member
Joined
Sep 14, 2015
Messages
22
Hi! I have this tricky problem I'm not capable of solving.

I have a cell containing a formula: =0 + (77*33) + (103*22) + (8*11) . What I would like to do is store the value before *33 in one variable, then store the value before *22 in a variable, and lastly the value before *11 in another variable. The values I want to store may change, but the formula will be the same. When all is run, I would like the result of the formula/or the formula to still be intact. Is there anyway to do this?

In this case:
Code:
Variable33 = 77
Variable22 = 103
Variable11 = 8
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
What is the point of the 0 + part? And do you mean with VBA code? See if you can adjust this:
Code:
Sub FormulaReplace()


    Dim strFrm  As String
    Dim x       As Long
    Dim variable(1 To 3) As Long
    
    strFrm = "=(@1*33) + (@2*22) + (@3*11)"
    
    variable(1) = 77
    variable(2) = 103
    variable(3) = 8
    
    For x = LBound(variable) To UBound(variable)
        strFrm = Replace(strFrm, "@" & x, variable(x))
    Next x
    
    Cells(1, 1).Formula = strFrm
    Erase variable
    
End Sub
 
Upvote 0
Thanks for your input! The 0 + is just something another macro produces, and I just have to deal with that particular outcome.

I don't completely understand your code, but it looks like it's built to insert a specific variable into the formula, or am I wrong? I want to extract those parts and store them in variables (to later paste those values into different cells). Now the values are bundeled up in one cell and in one formula, I want to paste those values into 3 different cells. If that makes sense :)
 
Last edited:
Upvote 0
On a clean sheet, run the following and see if that helps:
Code:
Sub Coefficients()


    Dim str     As String
    Dim arr()   As String
    Dim x       As Long
    
    Cells(2, 1).Formula = "=0+(77*33)+(103*22)+(8*11)"
    
    str = Cells(2, 1).Formula
    str = Replace(str, "=0", 1)
    arr = Split(str, "+")
    
    For x = LBound(arr) To UBound(arr)
        If InStr(arr(x), "(") Then
            arr(x) = Mid$(arr(x), InStr(arr(x), "(") + 1, InStr(arr(x), "*") - InStr(arr(x), "(") - 1)
        End If
    Next x
        
    Application.ScreenUpdating = False
    
    With Cells(2, 2).Resize(, (UBound(arr) + 1))
        .Value = arr
        .Cells(1, 1).Copy
        .PasteSpecial xlPasteValues, operation:=xlMultiply
        With .Cells(1, 1)
            .ClearContents
            .Select
        End With
    End With
    
    Erase arr
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub
 
Upvote 0
What is the real formula? The one posted in the OP is a constant and =77 is a solution to that example, but not what you want.

What I would suggest is along the lines of, use Names to change the OP formula to = 0 + (var1*33) + (var2*22) + (var3*11)

and then use =var1 to extract the current value of that variable Or, in VBA,

Code:
MsgBox Evaluate(Names("var1").RefersTo))
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top