Math operation between a column with unique formulas and another column

[dejhantulip]

Hello guys!

So I've been having this situation quite often, and I have searched and thought of something to solve my problem but haven't been able to find anything.

I have a two columns. Column F and G.
In Column F I have several rows of information, each row is unique, some are values and some are formulas.
Because of the nature of the work I do, sometimes I need to "affect" each of the cells in Column F with a factor (multiplying, or dividing, or sometimes even adding or substracting) in Column G.

For example, as you can see in the attached image:
1) Cell F10 has a hard-entered number 0.60, in this case I would like the cell to be =(0.60)*G10
2) Cell F11 has the formula =E11*$J$13 and in this case I would like the cell to be =(E11*$J$13)*G11

That is the basic idea. I guess I would have to use some kind of VBA code... but I am not sure.
Anyone could help me out?

Thank you very much in advance!

 

[MARK858]

If you want it a bit more generic then select a cell in the column to be amended (in this case a cell column F) and as long as your multiplication factor is in the next column (in this case column G) then try the code below.

Sub MultConst2()
    Dim myCell As Range, myRng As Range, lr As Long
    
    lr = Columns(ActiveCell.Column).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))
    
    For Each myCell In myRng
        If Not myCell.HasFormula Then
            myCell.Formula = "=" & myCell.Value & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
        Else
            myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
        End If
    Next

End Sub

 

[dejhantulip]

If you want it a bit more generic then select a cell in the column to be amended (in this case a cell column F) and as long as your multiplication factor is in the next column (in this case column G) then try the code below.

Sub MultConst2()
    Dim myCell As Range, myRng As Range, lr As Long
   
    lr = Columns(ActiveCell.Column).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))
   
    For Each myCell In myRng
        If Not myCell.HasFormula Then
            myCell.Formula = "=" & myCell.Value & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
        Else
            myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
        End If
    Next

End Sub

Thank you so much! I will test this as soon as I get home!

You have been very kind, I truly thank you for all the help

 

[MARK858]

If you want it a bit more generic then select a cell in the column to be amended (in this case a cell in column F) and as long as your multiplication factor is in the next column (in this case column G) then try the code below.

Thank you so much! I will test this as soon as I get home!

Better test the one below just in case you have any cells that are blank in the range...

Sub MultConst2()
    Dim myCell As Range, myRng As Range, lr As Long
  
    lr = Columns(ActiveCell.Column).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))
  
    For Each myCell In myRng
        If myCell.Value <> "" Then
            If Not myCell.HasFormula Then
                myCell.Formula = "=" & myCell.Value & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
            Else
                myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
            End If
        End If
    Next

End Sub

 

[dejhantulip]

Better test the one below just in case you have any cells that are blank in the range...

Sub MultConst2()
    Dim myCell As Range, myRng As Range, lr As Long
 
    lr = Columns(ActiveCell.Column).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))
 
    For Each myCell In myRng
        If myCell.Value <> "" Then
            If Not myCell.HasFormula Then
                myCell.Formula = "=" & myCell.Value & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
            Else
                myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*" & Split(myCell.Offset(, 1).Address(True, False), "$")(0) & myCell.Row
            End If
        End If
    Next

End Sub

Awesome! Works very well!! Could it be possible to adjust the code so that the code only runs on the selected range? Right now when I select only the data range, it is picking even the header of my table and multiplying with the G column. I would like to code to be applied only to the selected cells.

Thank you very much!!

 

[MARK858]

First of all the code only works from row 2 down in the code (so your headers can't all be in row one) so all you need to change is the 2 in

Cells(2, ActiveCell.Column)

to whatever row your data starts in.

If you want it to only work on the selection then just change

Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))

to

Set myRng = Selection

Just make sure that you select only cells in the same column.

 

[dejhantulip]

First of all the code only works from row 2 down in the code (so your headers can't all be in row one) so all you need to change is the 2 in

Cells(2, ActiveCell.Column)

to whatever row your data starts in.

If you want it to only work on the selection then just change

Set myRng = Range(Cells(2, ActiveCell.Column), Cells(lr, ActiveCell.Column))

to

Set myRng = Selection

Just make sure that you select only cells in the same column.

Great!! You rock!! Thanks a bunch!!

One last thing, and small modification just in case I need it in the future... if for whatever reason I want do exactly what the code does with the only difference that I want to insert the multiplication directed to a SINGLE cell (maybe I could input the cell reference in a message box) how would that work in the code?

Basically, if I would like the entire selection to just multiply each row with cell M20.
So for instance, in F10 =0.60*$M$20 and in F11 =(E11*$J$13)*$M$20
so everything times $M$20, this would be like a "global" factor that applies to the selection.

Could I bother you for this last little modification of the code please?
Thank you so much for everything!

 

[MARK858]

You just change everything after the 2 "*" & for either
Range("M20") or Cells(20, "M20")

 

[dejhantulip]

You just change everything after the 2 "*" & for either
Range("M20") or Cells(20, "M20")

Thank you!!

As you can imagine, I have very little knowledge in VBA, I tried to implement a solution that after selecting the range I want to append the cell to, I would be asked to select the actual address of the formula to be applied.

I think I am missing something in the Range().address part of my code. Would you be kind enough to look at what I've done to see what I am doing wrong?
Everything that is commented (' ONB) is everything I've added or modified.

Sub AppendFormulaFromSingleCell()
    Dim myCell As Range, myRng As Range, lr As Long
  
    Dim xSRg As Range 'ONB
    On Error GoTo Err1 'ONB
  
    lr = Columns(ActiveCell.Column).Find("*", , xlValues, , xlByRows, xlPrevious).Row
    Set myRng = Selection
  
    Set xSRg = Application.InputBox("Select CELL to be multiplied to previously selected range:", "Test", xTxt, , , , , 8) 'ONB
    If xSRg Is Nothing Then 'ONB
Err1: 'ONB
    Application.ScreenUpdating = True 'ONB
    Exit Sub 'ONB
    End If 'ONB
  
    For Each myCell In myRng
        If myCell.Value <> "" Then
            If Not myCell.HasFormula Then
                myCell.Formula = "=" & myCell.Value & "*" & Range(xSRg).Address 'Range("$B$2").Address
            Else
                myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*" & Range(xSRg).Address 'Range("$B$2").Address
            End If
        End If
    Next

End Sub

Thank you very much!

 

[MARK858]

Just at a glance (not tested) try

xSRg.Address(1, 1)

rather than

Range(xSRg).Address

 

[dejhantulip]

Just at a glance (not tested) try

xSRg.Address(1, 1)

rather than

Range(xSRg).Address

Thank you!!
I put that in the code, but it is not working... it does nothing to the selected cells...