Insert formula based on adjacent columns in same row

mjtolent

New Member
Morning All,

Wondered if you could assist. I have a report that lists a load of projects (column A e.g.), with a division (column B) with the sales (column C) and costs (D) for the period. Column E has a calculation of the margin (column C-D) and column F is the profit% (column E/C).

The data is imported by a macro and then sorted and sub-totalled by division. The sales, costs and margin columns have a sum total by division inserted by the macro, but I'm struggling with the margin % as I can't just sum it as part of the macro (it needs to be the total margin for the division / divided the total sales for the division).

I created the code below to select the column that contains the blank cells where the formula needs to go at the end of each division, but I'm struggling how to make the formula look at the relevant cells on the same row.

VBA Code:
``````Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 1).Select

For Each cell In Selection
If cell.Value = vbNullString Then cell.formula =
Next cell``````

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Trixterz

Board Regular
You need to break down what you want to do step by step because I am confused to what your asking. Example take data from column A and put it in column C on the same row.

mjtolent

New Member
Apologies.
In the subtotal line for each division, in Column F, I need a calculation of column E divided by Column C

Trixterz

Board Regular
Try this code then...
VBA Code:
``Range("F" & ActiveCell.Row).Value = Range("E" & ActiveCell.Row).Value / Range("C" & ActiveCell.Row).Value``

mjtolent

New Member

Thanks for taking the time Trixterz, although I'm struggling to incorporate this code into a loop (getting an invalid call procedure error amongst others)

VBA Code:
``````Range("L5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Offset(0, 1).Select

Dim myrange As Range
myrange = Selection

For Each cell In myrange
If cell.Value = vbNullString Then myrange("M" & ActiveCell.Row).Value = myrange("L" & ActiveCell.Row).Value / myrange("K" & ActiveCell.Row).Value
Next cell``````

How can I get the vba to check my range for blank cells and then use your code to apply the calculation?
Thanks

mj

Trixterz

Board Regular
There are many cells in the worksheet, you need to be more specific on what cells you want to check?

mjtolent

New Member

OK, so I'm selecting all values in column L using
VBA Code:
``````Range("L5").Select
Range(Selection, Selection.End(xlDown)).Select``````

I'm then moving across to column M using (column that contains blank cells)
Code:
``Selection.Offset(0, 1).Select``

Column M contains a calculation for every row within the selected range apart from on the subtotal rows. I want excel to find those blank cells and apply the calculation

Trixterz

Board Regular
So if column M is a blank cell... what calculation are you referring to that you want to apply to column M?

mjtolent

New Member
Value on the same row in column L divided by the value on the same row in column K

Replies
5
Views
90
Replies
2
Views
138
Replies
5
Views
129
Replies
12
Views
99
Replies
3
Views
166