Announcement

Collapse
No announcement yet.

give me for loop for this

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • give me for loop for this



    Hi Team,

    Thanks in advance


    I have multiple variable which individually value by below with my required criteria auto filters

    count1 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count

    filter2

    count2 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count

    filter3

    count3 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count

    and go on...............


    and for that I have defined below variable

    Dim count, count1, count2, count3, count4, count5, count6, count7
    Dim count8, count9, count10, count11, count12, count13, count14
    Dim count15, count16, count17, count18, count19, count20 as long

    these each 20 count need to take from 4 excel files and then store to the one new file. after completion of one excel for next excel I have to redefine above variable like


    count1 = 0
    count2 = 0
    count3 = 0
    count4 = 0
    count5 = 0
    count6 = 0
    count7 = 0
    count8 = 0
    count9 = 0
    count10 = 0
    count11 = 0
    count12 = 0
    count13 = 0
    count14 = 0
    count15 = 0
    count16 = 0
    count17 = 0
    count18 = 0
    count19 = 0
    count20 = 0

    is there any idea seniors to create loop for that..

    thanks so much will b very great full

  • #2
    You need function
    =test(a1:a10,"your text as criteria)
    Code:
    Function test(rg as range,cr as string)
    dim c as range
    with CreateObject("System.Collections.Arraylist")
      for each c in rg
         if not c.visible = false then
            If instr(1,cr,c.value,1) then
               .add c.value
            end if
        end if
     next c
    test = .count:.clear
    end with
    end function

    Comment


    • #3
      Originally posted by graha_karya View Post
      You need function
      =test(a1:a10,"your text as criteria)
      Code:
      Function test(rg as range,cr as string)
      dim c as range
      with CreateObject("System.Collections.Arraylist")
      for each c in rg
      if not c.visible = false then
      If instr(1,cr,c.value,1) then
      .add c.value
      end if
      end if
      next c
      test = .count:.clear
      end with
      end function


      there stored variable counts saved like below


      ActiveCell.Offset(0, 0) = count2
      ActiveCell.Offset(0, 1) = count1
      ActiveCell.Offset(0, 3) = count4
      ActiveCell.Offset(0, 4) = count3
      ActiveCell.Offset(5, 0) = sum2
      ActiveCell.Offset(5, 1) = sum1
      ActiveCell.Offset(5, 3) = Sum4
      ActiveCell.Offset(5, 4) = Sum3
      ActiveCell.Offset(5, 9) = Fname


      ActiveCell.Offset(10, 0) = count6
      ActiveCell.Offset(10, 1) = count5
      ActiveCell.Offset(10, 3) = count8
      ActiveCell.Offset(10, 4) = count7
      ActiveCell.Offset(15, 0) = Sum6
      ActiveCell.Offset(15, 1) = Sum5
      ActiveCell.Offset(15, 3) = Sum8
      ActiveCell.Offset(15, 4) = Sum7

      ActiveCell.Offset(15, 9) = Fname

      ActiveCell.Offset(53, 0) = count10
      ActiveCell.Offset(53, 1) = count9
      ActiveCell.Offset(53, 3) = count12
      ActiveCell.Offset(53, 4) = count11
      ActiveCell.Offset(58, 0) = count14
      ActiveCell.Offset(58, 1) = count13
      ActiveCell.Offset(58, 3) = count16
      ActiveCell.Offset(58, 4) = count15
      ActiveCell.Offset(63, 0) = count18
      ActiveCell.Offset(63, 1) = count17
      ActiveCell.Offset(63, 3) = count20
      ActiveCell.Offset(63, 4) = count19

      Comment


      • #4
        Your variabel only sum and count
        than change this
        Code:
         Function test(rg as range,cr as string,fd as boolean)
        dim c as range
        with CreateObject("Scripting.dictionary")
          for each c in rg
              if not c.visible = false then
                If instr(1,cr,c.value,1) then
                    if not .exists(c.value) then
                        .add c.value , array(1,c.value)
                    else
                       .item(c.value)=array(.item(c.value)(0) +1,.item(c.value)(1)+val(c.value))
                    end if
               end if
             end if
         next c
        If fd = true then test=.item(cr)(0) else test=.item(cr)(1)
        end with
        end function

        Comment


        • #5


          Originally posted by graha_karya View Post
          Your variabel only sum and count
          than change this
          Code:
          Function test(rg as range,cr as string,fd as boolean)
          dim c as range
          with CreateObject("Scripting.dictionary")
          for each c in rg
          if not c.visible = false then
          If instr(1,cr,c.value,1) then
          if not .exists(c.value) then
          .add c.value , array(1,c.value)
          else
          .item(c.value)=array(.item(c.value)(0) +1,.item(c.value)(1)+val(c.value))
          end if
          end if
          end if
          next c
          If fd = true then test=.item(cr)(0) else test=.item(cr)(1)
          end with
          end function


          hi plz can u attached xlsm of above

          Comment

          Working...
          X