Posts by Rmrekoj

Rmrekoj · Dec 7th 2018

Plz updat sir...

Rmrekoj · Dec 7th 2018

yes dude appreciate....awesome your work.. .huge clap for you....

just final request can I get all output into variable, as m working on a project so I can store the value each criteria each position where I want.....

Rmrekoj · Dec 7th 2018

I found the below function on other forum.. but it working only for the a single column ..
please can you work on it and take all our 4 column in data and result as we want

also if possible each criteria count and data name can I have In variable like ...below

dim strm1,strm2,strm3,strm4 as string
dim vlu1, vlu2,vlu3......till vlu16 as long

so I can position them as I want..

thanks for your help...

Sub naya()

Summarize Sheet1.Range("A2:A9"), Sheet1.Range("C1")

End Sub

Function Summarize(rngSource As Range, rngTarget As Range)

Dim rng As Range
Dim var As Variant
Set d = CreateObject("Scripting.Dictionary")
For Each rng In rngSource
If rng <> "" Then
If d.Exists(rng.Value) Then
d(rng.Value) = d(rng.Value) + 1
Else
d.Add rng.Value, 1
End If
End If
Next rng

rngTarget = "Value"
rngTarget.Offset(, 1) = "Count"
Set rng = rngTarget.Offset(1)
For Each var In d.Keys
rng = var
rng.Offset(, 1) = d(var)
Set rng = rng.Offset(1)
Next
End Function

Rmrekoj · Dec 7th 2018

Hi Dude,

after run above the above macro excel get crashed..
after several minutes finally in my hand got the 0 count
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 240"]

[tr]

[TD="width: 64, bgcolor: transparent"]D1[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]0[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]D3[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]D2[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]D5[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]

[/tr]

[/TABLE]

and the formula pasted till row (1048576)..

Rmrekoj · Dec 7th 2018

hi Senior ,

thanks for the help but m facing the compile error####

Rmrekoj · Dec 7th 2018

I am trying to find the number of same values are repeated in particular column. Then I need to paste the value and the count of the repeated value in column A,B,C,D of another sheet. I need to count the number of times repeated values: data is like below... [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 179"]

[tr]

[td]

Data1

[/td]

[td]

Data2

[/td]

[td]

Data3

[/td]

[td]

Data4

[/td]

[/tr]

[tr]

[td]

D1

[/td]

[td]

D3

[/td]

[td]

D1

[/td]

[td]

D1

[/td]

[/tr]

[tr]

[td]

D1

[/td]

[td]

D3

[/td]

[td]

D1

[/td]

[td]

D1

[/td]

[/tr]

[tr]

[td]

D1

[/td]

[td]

D2

[/td]

[td]

D1

[/td]

[td]

D3

[/td]

[/tr]

[tr]

[td]

D2

[/td]

[td]

D2

[/td]

[td]

D3

[/td]

[td]

D3

[/td]

[/tr]

[tr]

[td]

D2

[/td]

[td]

D3

[/td]

[td]

D2

[/td]

[td]

D5

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

D1

[/td]

[td]

D1

[/td]

[td]

D3

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

D2

[/td]

[td]

D1

[/td]

[td]

D1

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

D2

[/td]

[td]

D1

[/td]

[td]

D3

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

D3

[/td]

[td]

D3

[/td]

[td]

D3

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

D3

[/td]

[td]

D1

[/td]

[td]

D3

[/td]

[/tr]

[/TABLE]

and result should get like below [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 227"]

[tr]

[td]

Data

[/td]

[td]

Count1

[/td]

[td]

Count1

[/td]

[td]

Count3

[/td]

[td]

Count4

[/td]

[/tr]

[tr]

[td]

D1

[/td]

[td]

3

[/td]

[td]

1

[/td]

[td]

7

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

D2

[/td]

[td]

2

[/td]

[td]

4

[/td]

[td]

1

[/td]

[td]

0

[/td]

[/tr]

[tr]

[td]

D3

[/td]

[td]

5

[/td]

[td]

5

[/td]

[td]

2

[/td]

[td]

6

[/td]

[/tr]

[tr]

[td]

D5

[/td]

[td]

0

[/td]

[td]

0

[/td]

[td]

0

[/td]

[td]

1

[/td]

[/tr]

[/TABLE]

Rmrekoj · Dec 7th 2018

thanks for the help , but sir I really need the same working to be on Macros. isn't it possible ,

Rmrekoj · Dec 7th 2018

I think you have forgot to convert the excel into xlsm

Rmrekoj · Dec 6th 2018

thanks in advance...

Rmrekoj · Dec 6th 2018

Hi Sir,

I have a data in column A:A which I need to result other sheet

data is like... [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 125"]

[tr]

[td]

Product 1

[/td]

[td]

Product 2

[/td]

[/tr]

[tr]

[TD="align: right"]101[/TD]
[TD="align: right"]102[/TD]

[/tr]

[tr]

[TD="align: right"]102[/TD]
[TD="align: right"]189[/TD]

[/tr]

[tr]

[TD="align: right"]101[/TD]
[TD="align: right"]101[/TD]

[/tr]

[tr]

[TD="align: right"]102[/TD]
[TD="align: right"]102[/TD]

[/tr]

[tr]

[TD="align: right"]107[/TD]
[TD="align: right"]101[/TD]

[/tr]

[tr]

[TD="align: right"]101[/TD]
[TD="align: right"]189[/TD]

[/tr]

[tr]

[TD="align: right"]101[/TD]
[TD="align: right"]101[/TD]

[/tr]

[tr]

[TD="align: right"]189[/TD]
[TD="align: right"]102[/TD]

[/tr]

[/TABLE]

and the result should get of that like below...
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 131"]

[tr]

[td]

Data

[/td]

[td]

Product1

[/td]

[td]

Product2

[/td]

[/tr]

[tr]

[td]

101

[/td]

[td]

4

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

102

[/td]

[td]

2

[/td]

[td]

3

[/td]

[/tr]

[tr]

[td]

107

[/td]

[td]

1

[/td]

[td][/td]

[/tr]

[tr]

[td]

189

[/td]

[td]

1

[/td]

[td]

2

[/td]

[/tr]

[/TABLE]

plz help regarding this query

Rmrekoj · Dec 5th 2018

credit taken by u only

Rmrekoj · Dec 5th 2018

[TABLE="class: text_table, width: 704"]

[tr]

[TD="colspan: 3"] Respected Sir,

according my above vba...

is there any shortcut or loop available in your sharp database to use for below ????
[/TD]

[td][/td]

[/tr]

[tr]

[td]

as its getting huge...

[/td]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 0) = count2
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 1) = count1
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 3) = count4
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 4) = count3
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 0) = sum2
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 1) = sum1
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 3) = Sum4
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 4) = Sum3
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 9) = Fname
[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 0) = count6
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 1) = count5
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 3) = count8
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 4) = count7
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 0) = Sum6
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 1) = Sum5
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 3) = Sum8
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 4) = Sum7
[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 9) = Fname
[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 0) = count10
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 1) = count9
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 3) = count12
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 4) = count11
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 0) = count14
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 1) = count13
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 3) = count16
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 4) = count15
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 0) = count18
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 1) = count17
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 3) = count20
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 4) = count19
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 0) = count22
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 1) = count21
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 3) = count24
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 4) = count23
[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(1, 0).Activate
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count1 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count2 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count3 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count4 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count5 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count6 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count7 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count8 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count9 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count10 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count11 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count12 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count13 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count14 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count15 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count16 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count17 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count18 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count19 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count20 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count21 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count22 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count23 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count24 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] sum1 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] sum2 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum3 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum4 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum5 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum6 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum7 = 0
[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum8 = 0
[/TD]

[/tr]

[/TABLE]

Rmrekoj · Dec 5th 2018

thanks so much SIR its working....

Rmrekoj · Dec 5th 2018

THANKS CARIM

m really sorry but m new in vba and learning from seniors like u ,,,

can u help me how to call above your function to below

[TABLE="class: text_table, width: 96"]

[tr]

[TD="width: 64, bgcolor: yellow"][SIZE=10px]A[/SIZE][/TD]
[TD="width: 64, bgcolor: yellow"][SIZE=10px]B[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]N[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]1[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]N[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]2[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]N[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]4[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]N[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]1[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]N[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]3[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]Y[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]1[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]Y[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]2[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]Y[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]3[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]Y[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]1[/SIZE][/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"][SIZE=10px]Y[/SIZE][/TD]
[TD="bgcolor: transparent"][SIZE=10px]2[/SIZE][/TD]

[/tr]

[/TABLE]

below vba giving me "5" which is wrong for me

plz help
.AutoFilter field:=1, Criteria1:="y", Operator:=xlFilterValues

With ActiveSheet
uCount = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count
End With
Tags: None

Rmrekoj · Dec 5th 2018

Thanks for the quote but I have already created below vba just stuck over there [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 704"]

[tr]

[TD="colspan: 2"]Option Explicit[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"]Sub GetFilter()[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"]Dim path, d As String[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"]Dim Bbook As Workbook[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"]Dim t As Double[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 5"]Dim ragarea, rnData, rData, kData, pkData As Range[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"]Dim Fname As String[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"]Dim count, count1, count2, count3, count4, count5, count6, count7[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"]Dim count8, count9, count10, count11, count12, count13, count14[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"]Dim count15, count16, count17, count18, count19, count20, count21, count22, count23, count24 As Long[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"]Dim Cnt1, Sntl, Mntl, Gntl, Pntl As Variant[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"]Dim sum1, sum2, Sum3, Sum4, Sum5, Sum6, Sum7, Sum8 As Long[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"]Dim ex As Date[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"]Dim Ddtae As Date[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"]Dim dnum, Pdtae, Dly, Dlyn, Cnum, Tty, Esum, Toxi, moxi, marsh, Baci, EHS, EHS1, GPS, x, i As Integer[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] Set kData = .UsedRange[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] With kData[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=Pdtae, Criteria1:="<=" & ex, Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=Cnum, Criteria1:="<>", Criteria2:="<> 0", Operator:=xlFilterValues 'valid[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=marsh, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count17 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=marsh, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count18 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=marsh[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] 'data[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=Aci, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count21 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Aci, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count22 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=dnum[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=Aci[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] 'data[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Tty, Criteria1:="D*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=EHS1, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count13 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=EHS1, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count14 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=EHS1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=EHS, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] Mntl = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] Cnt1 = Mntl * 5[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] .AutoFilter field:=marsh, Criteria1:="Y", Criteria1:=">0", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] Gntl = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] count9 = Cnt1 + Gntl - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=marsh[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=GPS, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count10 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=GPS[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=EHS[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=moxi, Criteria1:="*Home*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 11"] If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count < 2 Then[/TD]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=moxi[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] End If[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count5 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum5 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=Dlyn, Criteria1:="*N*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count6 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum6 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=Dlyn[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Tty, Criteria1:="P*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count1 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] sum1 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=Dlyn, Criteria1:="*N*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count2 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] sum2 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter.ShowAllData[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=Pdtae, Criteria1:=">" & ex, Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] .AutoFilter field:=Cnum, Criteria1:="<>", Criteria2:="<> 0", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=marsh, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count19 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=marsh, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count20 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=marsh[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] 'data[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=Aci, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count23 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Aci, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count24 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=dnum[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=Aci[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] 'data[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Tty, Criteria1:="D*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] .AutoFilter field:=EHS1, Criteria1:="N", Operator:=xlOr, Criteria2:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count15 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=EHS1, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count16 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=EHS1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=EHS, Criteria1:="Y", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] Mntl = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] Sntl = Mntl * 5[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] .AutoFilter field:=marsh, Criteria1:="Y", Criteria1:=">0", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] Pntl = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] count11 = Sntl + Pntl - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=marsh[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=GPS, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count12 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=GPS[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=EHS[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=moxi, Criteria1:="*Home*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 11"] If ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count < 2 Then[/TD]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=moxi[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] End If[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count7 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum7 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 8"] .AutoFilter field:=Dlyn, Criteria1:="*N*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count8 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum8 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] .AutoFilter field:=Dlyn[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Tty, Criteria1:="P*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=dnum, Criteria1:="N", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count3 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum3 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] [/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 7"] .AutoFilter field:=Dlyn, Criteria1:="*N*", Operator:=xlFilterValues[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] With ActiveSheet[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 10"] count4 = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count - 1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 9"] Sum4 = Application.Sum(Columns(Esum).SpecialCells(xlCellTypeVisible))[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] End With[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 3"] Bbook.Close False[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 0) = count2[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 1) = count1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 3) = count4[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(0, 4) = count3[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 0) = sum2[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 1) = sum1[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 3) = Sum4[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 4) = Sum3[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(5, 9) = Fname[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 0) = count6[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 1) = count5[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 3) = count8[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(10, 4) = count7[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 0) = Sum6[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 1) = Sum5[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 3) = Sum8[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 4) = Sum7[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(15, 9) = Fname[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 0) = count10[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 1) = count9[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 3) = count12[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(53, 4) = count11[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 0) = count14[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 1) = count13[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 3) = count16[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(58, 4) = count15[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 0) = count18[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 1) = count17[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 3) = count20[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(63, 4) = count19[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 0) = count22[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 1) = count21[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 3) = count24[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(68, 4) = count23[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"] ActiveCell.Offset(1, 0).Activate[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count1 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count2 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count3 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count4 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count5 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count6 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count7 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count8 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count9 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count10 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count11 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count12 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count13 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count14 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count15 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count16 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count17 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count18 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count19 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count20 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count21 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count22 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count23 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] count24 = 0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] sum1 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] sum2 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum3 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum4 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum5 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum6 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum7 = 0[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Sum8 = 0[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td]

End If

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"]Application.EnableEvents = False[/TD]

[td][/td]

[/tr]

[tr]

[TD="colspan: 4"]Application.ScreenUpdating = False[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[TD="colspan: 2"] Fname = Dir()[/TD]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[tr]

[td][/td]

[/tr]

[/TABLE]

see above you will get the answer why I need vba only ...
also request you to can u make it in loop , I know I have created individual variable each , so do u help me out .. to make it easier

Rmrekoj · Dec 5th 2018

Hi team,

with the below example , if I filter first column by criteria "Y" then I want to count the unique value from the Row B Only

Result should be = 3

how can I get that plz help, thank in advance
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 96"]

[tr]

[TD="width: 64, bgcolor: yellow"]A[/TD]
[TD="width: 64, bgcolor: yellow"]B[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]2[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]4[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]N[/TD]
[TD="bgcolor: transparent"]3[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]2[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]3[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]1[/TD]

[/tr]

[tr]

[TD="bgcolor: transparent"]Y[/TD]
[TD="bgcolor: transparent"]2[/TD]

[/tr]

[/TABLE]

below vba giving me "5" which is wrong for me

plz help
.AutoFilter field:=1, Criteria1:="y", Operator:=xlFilterValues

With ActiveSheet
uCount = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.count
End With

Rmrekoj · Dec 4th 2018

Quote from Carim

Let's hope ... Parder will come back to the Forum ... and confirm he has solved his 'auto-populate' problem ... :wink:

finally parder got all optional solution of his question

Rmrekoj · Dec 4th 2018

Quote from graha_karya

Another option

Code

sub azz()
With columns(1).specialcells(2,1).offset(1)
With .offset(,1)
.formula="=IF(AND(A2>26000000,A2<60500000),""AUTOMATIC"",""MANUAL"")"
.value =.value
end with
end with
end sub

:wink:yup

Rmrekoj · Dec 1st 2018

Quote from graha_karya

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

Display More

hi plz can u attached xlsm of above

Rmrekoj · Nov 24th 2018

Quote from graha_karya

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

Display More

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

Posts by Rmrekoj

macros to find duplicates count in new sheet

macros to find duplicates count in new sheet

macros to find duplicates count in new sheet

macros to find duplicates count in new sheet

macros to find duplicates count in new sheet

macros to find duplicates count in new sheet

Need help to get count of the value

Need help to get count of the value

Need help to get count of the value

Need help to get count of the value

Count only unique value after Filter

Count only unique value after Filter

Count only unique value after Filter

Count only unique value after Filter

Count only unique value after Filter

Count only unique value after Filter

Auto populate cells with existing values

How to loop IF if range is unknown?

give me for loop for this

give me for loop for this