none
Update existing VBA code with next loop RRS feed

  • Question

  • Hi, i have code that do some calculation on 3 excel sheets and paste answer in dashboard rows. what i want to calculate hundreds of sheets with out using sheets name in tabname array. Is any one know next loop for this help me. thanks

    Here is code:

    <

    Private Sub CommandButton2_Click()
    '           Macro1 Macro
    '
                Dim TabNames As Variant, Ordinals As Variant
                TabNames = Array("4-16 - 4-22", "4-23 - 4-29", "4-30 - 5-6")
                Ordinals = Array("1st", "2nd", "3rd")

                For i = 0 To UBound(TabNames, 1)
                    Range("A5").Offset(i).Value = TabNames(i)
                    Range("B5").Offset(i).Value = Ordinals(i)
                    Range("I5").Offset(i).Formula = "=AVERAGE('" & "adt" & TabNames(i) & "'!$P:$P)"
                    Range("J5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">=""&1)"
                    Range("C5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames(i) & "'!$P:$P, '" & "adt" & TabNames(i) & "'!$P:$P, "">301"",'" & "adt" & TabNames(i) & "'!$P:$P, ""<480"")"
                    Range("D5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">""&301,'" & "adt" & TabNames(i) & "'!$P:$P,""<""&480)"
                    Range("F5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames(i) & "'!$P:$P, '" & "adt" & TabNames(i) & "'!$P:$P, "">=1"",'" & "adt" & TabNames(i) & "'!$P:$P, ""<300"")"
                    Range("G5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames(i) & "'!$P:$P,"">=""&1,'" & "adt" & TabNames(i) & "'!$P:$P,""<""&300)"
                Next

                Range("E5:E7,H5:H7,K5:K7").FormulaR1C1 = "=(R2C3-R[0]C[-2])*(R1C4*R[0]C[-1])"
    End Sub

       >         
    Saturday, August 1, 2015 6:30 PM

Answers

  • Re:  second question

    '---
    Private Sub CommandButton2_Click()
     'revision_2 August 01, 2015
      Dim i As Long
      Dim TabNames As String
      Dim Ordinals As Variant
     
      For i = 2 To Worksheets.Count
        TabNames = Worksheets(i).Name
        Worksheets(i).Range("A5").Offset(i).Value = TabNames
        
       Worksheets(i).Range("B5").Offset(i).Value = OrdinalNumber(i) '<<<<

        Worksheets(i).Range("I5").Offset(i).Formula = "'=AVERAGE('" & "adt" & TabNames & "'!$P:$P)"
        Worksheets(i).Range("J5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & _
                      TabNames & "'!$P:$P,"">=""&1)"
        Worksheets(i).Range("C5").Offset(i).Formula = "'=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">301"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<480"")"
        Worksheets(i).Range("D5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">""&301,'" & "adt" & TabNames & "'!$P:$P,""<""&480)"
        Worksheets(i).Range("F5").Offset(i).Formula = "'=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">=1"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<300"")"
        Worksheets(i).Range("G5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">=""&1,'" & "adt" & TabNames & "'!$P:$P,""<""&300)"
      Next 'i

      Worksheets(1).Range("E5:E7,H5:H7,K5:K7").FormulaR1C1 = "=(R2C3-R[0]C[-2])*(R1C4*R[0]C[-1])"
    End Sub
    '---

    Function OrdinalNumber(ByVal lngNum As Long) As String '<<<<
      Dim lngN As Long
      Const strSuffix As String = "stndrdthththththth"
      lngN = lngNum Mod 100
      If ((lngN > 9) And (lngN < 20)) Or (lngN Mod 10 = 0) Then
        OrdinalNumber = lngNum & "th"
      Else
        OrdinalNumber = lngNum & Mid$(strSuffix, ((lngN Mod 10) * 2) - 1, 2)
      End If
    End Function
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Sunday, August 2, 2015 4:51 AM

All replies

  • Re:  looping thru sheets with a for/next loop

    This should get you a little further along...
    '---
    Private Sub CommandButton2_Click()
     'revised August 01, 2015
      Dim i As Long
      Dim TabNames As String
      Dim Ordinals As Variant
     
    '= Array("1st", "2nd", "3rd")
    ' Ordinals - see...  http://www.cpearson.com/Excel/ordinal.aspx '<<<<

      For i = 2 To Worksheets.Count
        TabNames = Worksheets(i).Name
        Worksheets(i).Range("A5").Offset(i).Value = TabNames
        
    '   Range("B5").Offset(i).Value = Ordinals(i)
    '   see http://www.cpearson.com/Excel/ordinal.aspx                 '<<<<

        Worksheets(i).Range("I5").Offset(i).Formula = "=AVERAGE('" & "adt" & TabNames & "'!$P:$P)"
        Worksheets(i).Range("J5").Offset(i).Formula = "=COUNTIFS('" & "adt" & _
                      TabNames & "'!$P:$P,"">=""&1)"
        Worksheets(i).Range("C5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">301"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<480"")"
        Worksheets(i).Range("D5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">""&301,'" & "adt" & TabNames & "'!$P:$P,""<""&480)"
        Worksheets(i).Range("F5").Offset(i).Formula = "=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">=1"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<300"")"
        Worksheets(i).Range("G5").Offset(i).Formula = "=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">=""&1,'" & "adt" & TabNames & "'!$P:$P,""<""&300)"
      Next 'i

      Worksheets(1).Range("E5:E7,H5:H7,K5:K7").FormulaR1C1 = "=(R2C3-R[0]C[-2])*(R1C4*R[0]C[-1])"
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 6:06 PM
    Saturday, August 1, 2015 10:27 PM
  • Hi Jim Cone,

    Thanks for help. you give some comments for completing code, But problem is that i don't know too much about vba so i'm not able to complete it. I'm very thankful to you if you provide all code. 

    Thanks

    Sunday, August 2, 2015 12:38 AM
  • Re:  second question

    '---
    Private Sub CommandButton2_Click()
     'revision_2 August 01, 2015
      Dim i As Long
      Dim TabNames As String
      Dim Ordinals As Variant
     
      For i = 2 To Worksheets.Count
        TabNames = Worksheets(i).Name
        Worksheets(i).Range("A5").Offset(i).Value = TabNames
        
       Worksheets(i).Range("B5").Offset(i).Value = OrdinalNumber(i) '<<<<

        Worksheets(i).Range("I5").Offset(i).Formula = "'=AVERAGE('" & "adt" & TabNames & "'!$P:$P)"
        Worksheets(i).Range("J5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & _
                      TabNames & "'!$P:$P,"">=""&1)"
        Worksheets(i).Range("C5").Offset(i).Formula = "'=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">301"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<480"")"
        Worksheets(i).Range("D5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">""&301,'" & "adt" & TabNames & "'!$P:$P,""<""&480)"
        Worksheets(i).Range("F5").Offset(i).Formula = "'=AVERAGEIFS('" & "adt" & TabNames & _
                      "'!$P:$P, '" & "adt" & TabNames & "'!$P:$P, "">=1"",'" & "adt" & _
                      TabNames & "'!$P:$P, ""<300"")"
        Worksheets(i).Range("G5").Offset(i).Formula = "'=COUNTIFS('" & "adt" & TabNames & _
                      "'!$P:$P,"">=""&1,'" & "adt" & TabNames & "'!$P:$P,""<""&300)"
      Next 'i

      Worksheets(1).Range("E5:E7,H5:H7,K5:K7").FormulaR1C1 = "=(R2C3-R[0]C[-2])*(R1C4*R[0]C[-1])"
    End Sub
    '---

    Function OrdinalNumber(ByVal lngNum As Long) As String '<<<<
      Dim lngN As Long
      Const strSuffix As String = "stndrdthththththth"
      lngN = lngNum Mod 100
      If ((lngN > 9) And (lngN < 20)) Or (lngN Mod 10 = 0) Then
        OrdinalNumber = lngNum & "th"
      Else
        OrdinalNumber = lngNum & Mid$(strSuffix, ((lngN Mod 10) * 2) - 1, 2)
      End If
    End Function
    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    Sunday, August 2, 2015 4:51 AM