Announcement

Collapse
No announcement yet.

give me for loop for this

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

  • Rmrekoj
    started a topic give me for loop for this

    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

  • Rmrekoj
    replied
    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

    Leave a comment:


  • graha_karya
    replied
    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

    Leave a comment:


  • Rmrekoj
    replied
    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

    Leave a comment:


  • graha_karya
    replied
    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

    Leave a comment:

Working...
X