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

  • You need function
    =test(a1:a10,"your text as criteria)

  • You need function
    =test(a1:a10,"your text as criteria)




    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

  • Your variabel only sum and count
    than change this

  • Your variabel only sum and count
    than change this




    hi plz can u attached xlsm of above