Excel 2010 VBA - How to set formula in multiple cells ?

S

shiroyuki

New Member
Joined
Mar 14, 2016
Messages
2
Thank you in advance for any helps anyone can provide. My knowledge of VBA is very limited.
I have a list of vba codes that I have to update the cell references every month when new column was added for new month.
I am looking for a way to simplify those codes, so that instead of updating the range (column) for all of them, I only need to update a few.

Is there a way to simplify the codes below?
The range that will be change monthly (move up one column as new column were added for a new month), is the "K" and "H" as in example below. The rows stay the same.
This is referenced to several tables within a worksheets (with headers), that's why some rows were not included (for example, row 1 to 3 are headers, row 12 & 13 are empty, and row 14 & 15 are headers again, etc).
The iOffset is an integer declaration (set for cases to look at values in certain ranges)

Range("W4").Formula = "=AVERAGE(K4:OFFSET(H4,," & iOffset & "))"
Range("W5").Formula = "=AVERAGE(K5:OFFSET(H5,," & iOffset & "))"
Range("W6").Formula = "=AVERAGE(K6:OFFSET(H6,," & iOffset & "))"
Range("W7").Formula = "=AVERAGE(K7:OFFSET(H7,," & iOffset & "))"
Range("W8").Formula = "=AVERAGE(K8:OFFSET(H8,," & iOffset & "))"
Range("W9").Formula = "=AVERAGE(K9:OFFSET(H9,," & iOffset & "))"
Range("W10").Formula = "=AVERAGE(K10:OFFSET(H10,," & iOffset & "))"
Range("W11").Formula = "=AVERAGE(K11:OFFSET(H11,," & iOffset & "))"

Range("W22").Formula = "=AVERAGE(K22:OFFSET(H22,," & iOffset & "))"
Range("W23").Formula = "=AVERAGE(K23:OFFSET(H23,," & iOffset & "))"
Range("W24").Formula = "=SUM(W22:W23)"
Range("W25").Formula = "=AVERAGE(K25:OFFSET(H25,," & iOffset & "))"
Range("W26").Formula = "=AVERAGE(K26:OFFSET(H26,," & iOffset & "))"
Range("W27").Formula = "=SUM(W25:W26)"
Range("W28").Formula = "=AVERAGE(K28:OFFSET(H28,," & iOffset & "))"
Range("W29").Formula = "=AVERAGE(K29:OFFSET(H29,," & iOffset & "))"

Range("W36").Formula = "=AVERAGE(K36:OFFSET(H36,," & iOffset & "))"
Range("W37").Formula = "=AVERAGE(K37:OFFSET(H37,," & iOffset & "))"
Range("W38").Formula = "=AVERAGE(K38:OFFSET(H38,," & iOffset & "))"

Range("W51").Formula = "=SUM(K51:OFFSET(BH51,," & iOffset & "))"
Range("W52").Formula = "=SUM(K52:OFFSET(BH52,," & iOffset & "))"
Range("W53").Formula = "=SUM(K53:OFFSET(BH53,," & iOffset & "))"
Range("W54").Formula = "=SUM(K54:OFFSET(BH54,," & iOffset & "))"

Range("W61").Formula = "=SUM(K61:OFFSET(H61,," & iOffset & "))"
Range("W62").Formula = "=SUM(K62:OFFSET(H62,," & iOffset & "))"
Range("W63").Formula = "=SUM(K63:OFFSET(H63,," & iOffset & "))"
Range("W64").Formula = "=SUM(K64:OFFSET(H64,," & iOffset & "))"
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
Welcome to the Forum!

Here's one way you could cut down the code lines, and also get rid of the volatile OFFSET function calls.

Code: 
Range("W4:W11,W22:W29,W36:W38").Formula = "=AVERAGE(K4:" & Range("H4").Offset(, iOffset).Address(0, 0) & ")"
Range("W24,W27").Formula = "=SUM(W22:W23)"

Range("W51:W54").Formula = "=SUM(K51:" & Range("BH51").Offset(, iOffset).Address(0, 0) & ")"
Range("W61:W64").Formula = "=SUM(K51:" & Range("H61").Offset(, iOffset).Address(0, 0) & ")"

But why use VBA at all? With all these ranges hard-coded, you're going to have problems if you rearrange the rows and columns of your workbook in any way.

Instead, why not make iOffset an Excel name (i.e. rather than a VBA variable). Then you can just have Excel formulae, e.g.

W4: =AVERAGE(K4:OFFSET(H4,,iOffset)) which will adjust automatically if you start inserting/deleting rows/columns
 
Upvote 0
Thank you for the response, StephenCrump.

As for your suggestion to make iOffset an Excel name instead of a VBA variable, I don't think I understand how to get that done, as I am not familiar with it.
How do I turn a variable of cases into an excel name so I can use it in a formula?

Below is what the case variables that was declared for the iOffset.
(the iOffset value is a manual input via messagebox, the value should be one of the cases listed below)

Code: 
Dim iOffset As Integer

Select Case iOffset
    Case 1
        Range("D52").Value = "EOP Nov"
        Range("E52").Value = "EOP Dec"
        Range("E54").Value = "EOP Dec"
        Range("F54").Value = "FCST Jan"
        Range("D59").Value = "EOP Nov"
        Range("E59").Value = "EOP Dec"
        Range("E61").Value = "EOP Dec"
        Range("F61").Value = "FCST Jan"
    Case 2
        Range("D52").Value = "EOP Dec"
        Range("E52").Value = "EOP Jan"
        Range("E54").Value = "EOP Jan"
        Range("F54").Value = "FCST Feb"
        Range("D59").Value = "EOP Dec"
        Range("E59").Value = "EOP Jan"
        Range("E61").Value = "EOP Jan"
        Range("F61").Value = "FCST Feb"
    Case 3
        Range("D52").Value = "EOP Jan"
        Range("E52").Value = "EOP Feb"
        Range("E54").Value = "EOP Feb"
        Range("F54").Value = "FCST Mar"
        Range("D59").Value = "EOP Jan"
        Range("E59").Value = "EOP Feb"
        Range("E61").Value = "EOP Feb"
        Range("F61").Value = "FCST Mar"
    Case 4
        Range("D52").Value = "EOP Feb"
        Range("E52").Value = "EOP Mar"
        Range("E54").Value = "EOP Mar"
        Range("F54").Value = "FCST Apr"
        Range("D59").Value = "EOP Feb"
        Range("E59").Value = "EOP Mar"
        Range("E61").Value = "EOP Mar"
        Range("F61").Value = "FCST Apr"
    Case 5
        Range("D52").Value = "EOP Mar"
        Range("E52").Value = "EOP Apr"
        Range("E54").Value = "EOP Apr"
        Range("F54").Value = "FCST May"
        Range("D59").Value = "EOP Mar"
        Range("E59").Value = "EOP Apr"
        Range("E61").Value = "EOP Apr"
        Range("F61").Value = "FCST May"
    Case 6
        Range("D52").Value = "EOP Apr"
        Range("E52").Value = "EOP May"
        Range("E54").Value = "EOP May"
        Range("F54").Value = "FCST Jun"
        Range("D59").Value = "EOP Apr"
        Range("E59").Value = "EOP May"
        Range("E61").Value = "EOP May"
        Range("F61").Value = "FCST Jun"
    Case 7
        Range("D52").Value = "EOP May"
        Range("E52").Value = "EOP Jun"
        Range("E54").Value = "EOP Jun"
        Range("F54").Value = "FCST Jul"
        Range("D59").Value = "EOP May"
        Range("E59").Value = "EOP Jun"
        Range("E61").Value = "EOP Jun"
        Range("F61").Value = "FCST Jul"
    Case 8
        Range("D52").Value = "EOP Jun"
        Range("E52").Value = "EOP Jul"
        Range("E54").Value = "EOP Jul"
        Range("F54").Value = "FCST Aug"
        Range("D59").Value = "EOP Jun"
        Range("E59").Value = "EOP Jul"
        Range("E61").Value = "EOP Jul"
        Range("F61").Value = "FCST Aug"
    Case 9
        Range("D52").Value = "EOP Jul"
        Range("E52").Value = "EOP Aug"
        Range("E54").Value = "EOP Aug"
        Range("F54").Value = "FCST Sep"
        Range("D59").Value = "EOP Jul"
        Range("E59").Value = "EOP Aug"
        Range("E61").Value = "EOP Aug"
        Range("F61").Value = "FCST Sep"
    Case 10
        Range("D52").Value = "EOP Aug"
        Range("E52").Value = "EOP Sep"
        Range("E54").Value = "EOP Sep"
        Range("F54").Value = "FCST Oct"
        Range("D59").Value = "EOP Aug"
        Range("E59").Value = "EOP Sep"
        Range("E61").Value = "EOP Sep"
        Range("F61").Value = "FCST Oct"
    Case 11
        Range("D52").Value = "EOP Sep"
        Range("E52").Value = "EOP Oct"
        Range("E54").Value = "EOP Oct"
        Range("F54").Value = "FCST Nov"
        Range("D59").Value = "EOP Sep"
        Range("E59").Value = "EOP Oct"
        Range("E61").Value = "EOP Oct"
        Range("F61").Value = "FCST Nov"
    Case 12
        Range("D52").Value = "EOP Oct"
        Range("E52").Value = "EOP Nov"
        Range("E54").Value = "EOP Nov"
        Range("F54").Value = "FCST Dec"
        Range("D59").Value = "EOP Oct"
        Range("E59").Value = "EOP Nov"
        Range("E61").Value = "EOP Nov"
        Range("F61").Value = "FCST Dec"
End Select
 
Upvote 0
You could replace this second block of code with the more succinct:

Code: 
Dim sDate As Variant
Dim iOffset As Integer
iOffset = 1    'say

sDate = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

Range("D52,D59").Value = "EOP " & sDate((iOffset + 9) Mod 12)
Range("E52,E54,E59,E61").Value = "EOP " & sDate((iOffset + 10) Mod 12)
Range("F54,F61").Value = "FCST " & sDate(iOffset - 1)

But again, why use VBA when you can do this using simple formulae?

If you have an Excel named range called iOffset (say), then your formulae could include, for example:

D52: ="EOP " & TEXT(DATE(2016,iOffset-2,1),"mmm")
F54: ="FCST " & TEXT(DATE(2016,iOffset,1),"mmm")
etc

If you want to, you could populate this range using VBA, e.g.

Code: 
Dim vInput As Variant
vInput = Application.InputBox("Enter 1 to 12", , , , , , , 1)

If vInput Then
    With Worksheets("Sheet1").Range("A1")
        .Value = vInput
        .Name = "iOffset"
    End With
Else
    'User has cancelled
    'Do something else
End If
 
Upvote 0
You must log in or register to reply here.

Similar threads

4
  • Question
VBA - Dynamic Cell Shifting
Replies
4
Views
1K
4EverStudent
4
T
  • Question
VBA inserting formula's in cells not working
Replies
7
Views
331
Peter_SSs
Peter_SSs
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
129
KRice
K
E
  • Question
Average a range between 2 dates
Replies
3
Views
357
Fluff
Fluff
X
  • Solved
Problems when i add new rows in table via userform. Formulas wont get taken over and cell formats are changing
Replies
7
Views
349
Anthony47
A

Forum statistics

Threads
1,215,193
Messages
6,123,560
Members
449,108
Latest member
rache47

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top