# Math operation between a column with unique formulas and another column

#### dejhantulip

##### New Member
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!

#### Attachments

• 15.6 KB Views: 11

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### MARK858

##### MrExcel MVP
Select your cells in column G, copy, Select your cells in column F (must be the exact same size), PasteSpecial, check the Multiply option, click OK.

The F10 with be a hard coded value as a result, the F11 will be a formula.
Where there are blanks in column G then you will need a 1

#### dejhantulip

##### New Member
Select your cells in column G, copy, Select your cells in column F (must be the exact same size), PasteSpecial, check the Multiply option, click OK.

The F10 with be a hard coded value as a result, the F11 will be a formula.
Thank you very much! This gets the job done, however the the paste special pastes the values of column G.
For example, in F11 the formula ends up being =(E11*\$K\$13)*1.03
See what I mean? I would like that 1.03 to be G11. This is because I might change the G10 1.03 for 1.05 maybe, and I would like the dynamic relation to stay between the F10 and G10 cells.

I hope I was able to explain myself

Thank you!!

#### MARK858

##### MrExcel MVP
Then put 1 in your blank spaces in G, change your formula to =(E11*\$K\$13)*G11, then for the constants run
VBA Code:
``````Sub MultConst()
Dim myCell As Range
For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
If Not myCell.HasFormula Then
With myCell
.Offset(, 1).Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Application.CutCopyMode = False
End With
End If
Next
End Sub``````

#### dejhantulip

##### New Member
Then put 1 in your blank spaces in G, change your formula to =(E11*\$K\$13)*G11, then for the constants run
VBA Code:
``````Sub MultConst()
Dim myCell As Range
For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
If Not myCell.HasFormula Then
With myCell
.Offset(, 1).Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply
Application.CutCopyMode = False
End With
End If
Next
End Sub``````
Thank you for taking the time to help me out.

However, I am seeing that the VBA code will not be able to be used in a future workbook with different columns (I see that your code has hard-coded the F range and F2 cell i think). Also when I run the code I see that the paste value is performed, but ideally what I am looking for is to "append" the G column references to each of the F column in-cell values... so basically have a VBA to:

1. Select Column F (where I want the code applied)
2. Insert the "=" sign in case the column F cell doesn't have one
3. Insert an open parenthesis "(" after the "=" sign from step 1
4. Insert at the end of the cell a closing parenthesis ")"
(Note.- Now I can safely apply the factor)
5. Insert "*G<row number>" to the end of each cell in Column F... so in row 10 it would be -> =(<whatever is in cell F10>)*G10 and in row 11 it would be -> =(<whatever is in cell F11>)*G11 and so forth...

I would like, if at all possible and with all due respect, to get some code that I could use in the future and that would be applicable in different scenarios.

Thank you very much!

#### MARK858

##### MrExcel MVP
3. Insert an open parenthesis "(" after the "=" sign from step 1
4. Insert at the end of the cell a closing parenthesis ")"
Why do you need the ( and the ) in the cells with values? if a cell had the value 32 in F11 then the formula would simply be
`=32*G11` no need for `=(32*G11)` or `=(32)*G11` or `32*(G11)`

You also don't need brackets with your formula if it is =E11*\$J\$13

#### dejhantulip

##### New Member
Why do you need the ( and the ) in the cells with values? if a cell had the value 32 in F11 then the formula would simply be
`=32*G11` no need for `=(32*G11)` or `=(32)*G11` or `32*(G11)`
Just to make sure it works always in case I have the formula in Cell F10 like this =11-\$M\$14 where if I dont have the "(" and ")" the code would add the G10 term at the end and the cell would become =11-\$M\$14*G10 which would be wrong because the multiplication would take priority over the substraction. In this I would definately need =(11-\$M\$14)*G10

So basically just to be on the safe side always.

#### MARK858

##### MrExcel MVP
Just to make sure it works always in case I have the formula in Cell F10 like this =11-\$M\$14
You obviously didn't note that I asked why the brackets are needed in the cells with Values.

So basically just to be on the safe side always.
I will write the code based on what you have posted but being honest if this isn't a one-off then really you should review how the data is created because having mixed data of values and formulas in the same column is not a good idea

#### dejhantulip

##### New Member
You obviously didn't note that I asked why the brackets are needed in the cells with Values.

I will write the code based on what you have posted but being honest if this isn't a one-off then really you should review how the data is created because having mixed data of values and formulas in the same column is not a good idea
Ohh yes, I am sorry sir, I didnt notice the "values"word in your comments... you are correct, if it is values the parenthesis would not be needed.

I thank you very much for taking the time to help me with this problem. I do appreciate all of your help, thank you!!!!

#### MARK858

##### MrExcel MVP
VBA Code:
``````Sub MultConst()
Dim myCell As Range
For Each myCell In Range("F2:F" & Range("F" & Rows.Count).End(xlUp).Row)
If Not myCell.HasFormula Then
myCell.Formula = "=" & myCell.Value & "*G" & myCell.Row
Else
myCell.Formula = "=(" & Replace(myCell.Formula, "=", "") & ")" & "*G" & myCell.Row
End If
Next
End Sub``````

1,102,241
Messages
5,485,597
Members
407,504
Latest member
inexperiencedOne

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...