vba help - loop and update subtotal formula above headers

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am putting subtotal formula on once cell up on headers. there are 18 columns in Total.
by using loop my code will be shorter. I am using match function to find column headers.


VBA Code:
Sub Subtotal_Formula
Dim cInvoice as long
Dim cNotional as long
Dim lr_new as long

cInvoice = application.worksheetfunction.match("Invoice",range("a1:z1"),0)
cNotional = application.worksheetfunction.match("Invoice",range("a1:z1"),0)

lr_new = 2000

With ws_Master
    .Cells(2,cInvoice).FormulaR1C1 = "=SUBTOTAL(9,R2C" & cInvoice & ":R" & lr_new & "C" & cInvoice & ")"
    .Cells(2,cNotional).FormulaR1C1 = "=SUBTOTAL(9,R2C" & cNotional & ":R" & lr_new & "C" & cNotional & ")" and so on .......
end with



'------------convert above code [B]something [/B]like this------
Looking help in loop. 

   Dim AryColumn As Variant
   AryColumn = Array(cInvoice,cNotional)
  

   With ws_Master
      For i = 0 To UBound(AryColumn)
        .Cells(2, AryColumn(i)).FormulaR1C1 = "=SUBTOTAL(9,R2C" & AryColumn(i)  & ":R" & lr_new & "C" & AryColumn(i) & ")"
        .Cells(2, AryColumn(i)).FormulaR1C1 = "=SUBTOTAL(9,R2C" & AryColumn(i) & ":R" & lr_new & "C" & AryColumn(i) & ")"
      Next i

   End With

thanks
mg
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,739
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Mallesh()
   Dim Ary As Variant
   Dim i As Long, lr_new As Long
   Dim Fnd As Range
   
   lr_new = 2000
   Ary = Array("Invoice", "Notional")
   For i = 0 To UBound(Ary)
      Set Fnd = Range("A1:Z1").find(Ary(i), , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then
         Cells(2, Fnd.Column).FormulaR1C1 = "=SUBTOTAL(9,R3C" & Fnd.Column & ":R" & lr_new & "C" & Fnd.Column & ")"
      End If
   Next i
End Sub
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

Perfect ! It worked.

one more question -

Can we pass column no like below. here column no I found using match function.

VBA Code:
   Dim AryColumn As Variant
   AryColumn = Array(cInvoice,cNotional)  or 'Array(2,4)
  
   With ws_Master
      For i = 0 To UBound(AryColumn)
        .Cells(2, [B]AryColumn(i)[/B]).FormulaR1C1 = "=SUBTOTAL(9,R2C" & [B]AryColumn(i)[/B]  & ":R" & lr_new & "C" & [B]AryColumn(i)[/B] &
      Next i
end with

Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,739
Office Version
  1. 365
Platform
  1. Windows
Yes you can do that.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
853
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

Thanks once again for your help, both approach worked. (y)

I am not finding below button
to click on the "Mark as solution" button - - right next to the best post which is answering your question.


Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,739
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
I am not finding below button
to click on the "Mark as solution" button - - right next to the best post which is answering your question.
It's the the big tick mark to the right of the post.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,055
Messages
5,628,338
Members
416,311
Latest member
S991102

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
Top