Insert formula based on adjacent columns in same row

[mjtolent]

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.

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

 

[Trixterz]

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]

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

 

[Trixterz]

Try this code then...

Range("F" & ActiveCell.Row).Value = Range("E" & ActiveCell.Row).Value / Range("C" & ActiveCell.Row).Value

 

[mjtolent]

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)

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]

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

 

[mjtolent]

OK, so I'm selecting all values in column L using

Range("L5").Select
    Range(Selection, Selection.End(xlDown)).Select

I'm then moving across to column M using (column that contains blank cells)

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]

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

 

[mjtolent]

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