Insert formula based on adjacent columns in same row

mjtolent

New Member
Joined
Oct 1, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Aug 15, 2019
Messages
59
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
Joined
Oct 1, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Aug 15, 2019
Messages
59
Try this code then...
VBA Code:
Range("F" & ActiveCell.Row).Value = Range("E" & ActiveCell.Row).Value / Range("C" & ActiveCell.Row).Value
 

mjtolent

New Member
Joined
Oct 1, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 15, 2019
Messages
59
There are many cells in the worksheet, you need to be more specific on what cells you want to check?
 

mjtolent

New Member
Joined
Oct 1, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 15, 2019
Messages
59
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
Joined
Oct 1, 2020
Messages
12
Office Version
  1. 2013
Platform
  1. Windows
Value on the same row in column L divided by the value on the same row in column K
 

Watch MrExcel Video

Forum statistics

Threads
1,119,062
Messages
5,575,878
Members
412,689
Latest member
nhsmedic
Top