vba help - combine two loops in a Single loop

[Mallesh23]

Hi Team,

I am putting subtotal formula above the header. below code works but I want to put both code into single loop.

'Situation on put formula on Second Row  - [B]Cells(2[/B], ary1(i)).FormulaR1C1

ary1 = Array(2, 6, 8) ' put formula on second Row
   For i = 0 To UBound(ary1)
         Cells(2, ary1(i)).FormulaR1C1 = "=SUBTOTAL(9,R4C" & ary1(i) & ":R" & lr_new & "C" & ary1(i) & ")"
   Next i
  
'Put formula on third Row - [B]Cells(3,[/B] ary1(i)).FormulaR1C1

ary1 = Array(3,7) 'Column 3 and 7, put formula on 3rd Row
For i = 0 To UBound(ary1)
         Cells(3, ary1(i)).FormulaR1C1 = "=SUBTOTAL(9,R4C" & ary1(i) & ":R" & lr_new & "C" & ary1(i) & ")"
Next i

Thanks
mg

 

[Fluff]

How about

   Ary = Array(2, 2, 3, 3, 6, 2, 7, 3, 8, 2)
   For i = 0 To UBound(Ary) Step 2
         Cells(Ary(i + 1), Ary(i)).FormulaR1C1 = "=SUBTOTAL(9,R4C" & Ary(i) & ":R" & lr_new & "C" & Ary(i) & ")"
   Next i

 

[Mallesh23]

Hi Fluff,

It worked !Thanks for your help, ... I tried below code I used select case, getting answer.

ary1 = Array(2, 6, 8 ,9,10,14) ' put formula on second Row
   For i = 0 To UBound(ary1)
   select Case Ary(i)
     case 2,6,8
         Cells(2, ary1(i)).FormulaR1C1 = "=SUBTOTAL(9,R4C" & ary1(i) & ":R" & lr_new & "C" & ary1(i) & ")"
    Case else
       Cells([B]3[/B], ary1(i)).FormulaR1C1 = "=SUBTOTAL(9,R4C" & ary1(i) & ":R" & lr_new & "C" & ary1(i) & ")"
end select

   Next i

Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.