# automate a formula

#### oddworld

##### Active Member
Hi all can somebody please give me a hand, i am trying to automate a formula that is changed each month.

I have a sheet with strength (no of pers) in column b
i have no of separations in column c

in a1 i have the following formula - =SUM(C202:C213)/((((B201+B213)/2)+B202+B203+B204+B205+B206+B207+B208+B209+B210+B211+B212)/12)

c202:c213 12 months of seps data/2
b201+b213 is the first and last month over 13 months
+b202 through b212 is the remaining 11 months strength
This formula gives me a rolling separation percentage.

I need vba code to automatically select the last 13 months on data in column b and last 12 months data in column c and apply my current formula, this will save me to change the range each month , more importantly i will leave no room for error.

any ideas would be appreciated.
Cheers

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Hi
Try

Code:
``````Sub aaa()
lastrow = Cells(Rows.Count, 2).End(xlUp).Row
Range("A1").Formula = "=SUM(C" & lastrow - 11 & ":C" & lastrow & ")/(((AVERAGE(B" & lastrow - 12 & ",B" & lastrow & ")+SUM(B" & lastrow - 11 & ":B" & lastrow - 1 & "))/12))"
End Sub``````

I've compressed the formula a bit to make life easier.

Tony

re

Thanks acw works a treat

cheers

re

just one last question, your code is working great on sheets that have the data in columns, how would i change your code to read similar data but in rows ie the strength is in row 53 and the separations are in row 61.
I don't want to transpose the data if i can hepl it.

Hi

I'm feeling lazy. What is your formula for the transposed data.

Tony

re

in A1 I HAVE =SUM(Q28:AB28)/(((AVERAGE(P27,AB27)+SUM(Q27:AA27))/12))

Hi

Try

Code:
``````Sub bbb()
lastcol = Cells(27, Columns.Count).End(xlToLeft).Column
Range("A1").Formula = "=SUM(r28c" & lastcol - 11 & ":r28c" & lastcol & ")/(((AVERAGE(r27c" & lastcol - 12 & ",r27c" & lastcol & ")+SUM(r27c" & lastcol - 11 & ":r27c" & lastcol - 1 & "))/12))"
Range("A1").Formula = WorksheetFunction.Substitute(Range("r24").Formula, "\$", "")
End Sub``````

Tony

re

Thank you for your time and effort, i couldn't get your code to work however the following code seems to work

Sub test()
LastCol = Cells(27, Columns.Count).End(xlToLeft).Column
Range("h20").Formula = "=SUM(" & Cells(28, LastCol - 11).Resize(, 12).Address & ") / (((AVERAGE(" & Cells(27, LastCol - 12).Resize(, 12).Address & ")+ SUM(" & Cells(27, LastCol - 11).Resize(, 11).Address & "))/12)) "
End Sub

cheers

Replies
12
Views
1K
Replies
7
Views
408
Replies
2
Views
961
Replies
2
Views
438
Replies
2
Views
912

1,217,254
Messages
6,135,490
Members
449,942
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back