vba help - loop and update subtotal formula above headers

[Mallesh23]

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.

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

 

[Fluff]

How about

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]

Hi Fluff,

Perfect ! It worked.

one more question -

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

   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]

Yes you can do that.

 

[Mallesh23]

Hi Fluff,

Thanks once again for your help, both approach worked.

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]

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.