VBA help

Emmily · Apr 15, 2011

Hi, is there a macro that will produce the below result. For each team i want the count of entries per age criteria as reflected in row 1 and then i want the amount in (AUD) and then the number of entries which does not have a comment in col P and it value in AUD.

I know all of this can be done via formula, but i will have another 20 teams and the last age criteria will go to >90, so there will be a lot of formulas, hence it will slow the performance of the workbook.

Col L-P is raw data and R-S is FX data

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

2-5

6-29

2

Team

No.of items

Value (AUD)

No. of items without Comments

Value (AUD)

No.of items

Value (AUD)

No. of items without Comments

Value (AUD)

Amount

CCY

Age

Source

Comments

FX RATES

Rate (AUD)

3

CMP1

1

11,593.66

1

5,796.83

0

0.00

0

0.00

8,268.57

AUD

1456

TRACES

AED

3.712435

4

TRACES

20,000.00

AUD

1248

TRACES

ANG

1.809243

5

LEHMAN

1,000.00

AUD

1248

TRACES

ARS

4.053867

6

4,417.80

GBP

3

CMP1

Test

ATS

10.203373

7

2,208.90

GBP

1018

CMP1

AUD

1

8

100,000.00

AUD

742

TRACES

BEF

29.912398

9

1,163.22

EUR

561

LEHMAN

BGN

1.450275

10

-3,320.14

EUR

561

LEHMAN

GBP

0.381053

11

9,646.51

EUR

561

LEHMAN

Test

BMD

1.01075

12

-12,618.80

EUR

561

LEHMAN

BRL

1.688256

13

476,017.76

EUR

561

LEHMAN

BSD

1.01075

14

-1,688,137.00

JPY

561

LEHMAN

BVD

2

15

7,187,517.00

JPY

561

LEHMAN

CAD

1.001098

16

-6,303,971.00

JPY

561

LEHMAN

CHF

0.957838

17

108,431.34

USD

561

LEHMAN

18

19

20

Sheet1

Emmily · Apr 16, 2011

*bump*

Krishnakumar · Apr 16, 2011

Hi,

Try this

Code:

Sub kTest()
    Dim ka, k(), i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age, dic2 As Object, t(), Hdr
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"    'assume it's in Source Sheet
    Const AgeCritRange          As String = "U3:V7"     'assume it's in Source Sheet
    Const DestShtName           As String = "Sheet2"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("Team", "No. of items", "Value (AUD)", "No. of items without Comments")
    
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
        Age = .Range(CStr(AgeCritRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    c = UBound(Age, 1) * 4 + 4
    
    ReDim k(1 To UBound(ka, 1), 1 To c)
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & ",'" & SourceShtName & "'!" & AgeCritRange & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, Idx * 4 - 4 + 1) = ka(i, 4)
                k(n, Idx * 4 - 4 + 2) = 1
                If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                k(n, Idx * 4 - 4 + 4) = IIf(Len(ka(i, 5)), 1, 0)
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                For m = 1 To c - 4 Step 4
                    k(t(0), m) = ka(i, 4)
                Next
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If CSng(dic1.Item(ka(i, 2))) Then
                    k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                If Len(ka(i, 5)) Then k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(DestShtName)).Range(CStr(DestRange))
            .EntireRow.NumberFormat = "@"
            m = 0
            For i = 1 To c Step 4
                m = m + 1
                If m = UBound(Age, 1) Then
                    .Offset(, i - 1).Value = ">" & Age(m, 1)
                    .Offset(1, i - 1).Resize(, 4).Value = Hdr
                    Exit For
                Else
                    .Offset(, i - 1).Value = Age(m, 1) & "-" & Age(m + 1, 1) - 1
                End If
                .Offset(1, i - 1).Resize(, 4).Value = Hdr
            Next
            .Offset(2).Resize(n, c).Value = k
        End With
    End If
        
End Sub

Setup an Age range like the following

Age Index
2 1
6 2
30 3
51 4
91 5

HTH

Emmily · Apr 16, 2011

Kris,

I ran your macro but nothing happened, why in the macro you have this, my Dest is sheet1, and what is stored in U3:V7?

My age criteiria is 2-5, 6-29,30-59,60-89 and >=90

Const AgeCritRange As String = "U3:V7" 'assume it's in Source Sheet
Const DestShtName As String = "Sheet2"
Const DestRange As String = "A1"

Krishnakumar said:

Hi,

Try this

Code:

Sub kTest()
    Dim ka, k(), i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age, dic2 As Object, t(), Hdr
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"    'assume it's in Source Sheet
    Const AgeCritRange          As String = "U3:V7"     'assume it's in Source Sheet
    Const DestShtName           As String = "Sheet2"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("Team", "No. of items", "Value (AUD)", "No. of items without Comments")
    
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
        Age = .Range(CStr(AgeCritRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    c = UBound(Age, 1) * 4 + 4
    
    ReDim k(1 To UBound(ka, 1), 1 To c)
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & ",'" & SourceShtName & "'!" & AgeCritRange & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, Idx * 4 - 4 + 1) = ka(i, 4)
                k(n, Idx * 4 - 4 + 2) = 1
                If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                k(n, Idx * 4 - 4 + 4) = IIf(Len(ka(i, 5)), 1, 0)
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                For m = 1 To c - 4 Step 4
                    k(t(0), m) = ka(i, 4)
                Next
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If CSng(dic1.Item(ka(i, 2))) Then
                    k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                If Len(ka(i, 5)) Then k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(DestShtName)).Range(CStr(DestRange))
            .EntireRow.NumberFormat = "@"
            m = 0
            For i = 1 To c Step 4
                m = m + 1
                If m = UBound(Age, 1) Then
                    .Offset(, i - 1).Value = ">" & Age(m, 1)
                    .Offset(1, i - 1).Resize(, 4).Value = Hdr
                    Exit For
                Else
                    .Offset(, i - 1).Value = Age(m, 1) & "-" & Age(m + 1, 1) - 1
                End If
                .Offset(1, i - 1).Resize(, 4).Value = Hdr
            Next
            .Offset(2).Resize(n, c).Value = k
        End With
    End If
        
End Sub

Setup an Age range like the following

Age Index
2 1
6 2
30 3
51 4
91 5

HTH

Emmily · Apr 16, 2011

I see what you have done with U3:V7, but the criteria is still not correct, i require the macro to return result in Sheet1, the teams on the left should stay as they are

Also all these 4 headers is for one Age criteria, so it will be 4* these headers for all the age criterias

<table border="0" cellpadding="0" cellspacing="0" width="431"><col width="77"><col width="78"><col width="198"><col width="78"><tr height="19"> <td class="xl63" style="height: 14.4pt; width: 58pt;" height="19" width="77">No.of items</td> <td class="xl63" style="border-left: medium none; width: 58pt;" width="78">Value (AUD)</td> <td class="xl63" style="border-left: medium none; width: 149pt;" width="198">No. of items without Comments</td> <td class="xl63" style="border-left: medium none; width: 58pt;" width="78">Value (AUD)</td> </tr></table>

Krishnakumar said:

Hi,

Try this

Code:

Sub kTest()
    Dim ka, k(), i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age, dic2 As Object, t(), Hdr
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"    'assume it's in Source Sheet
    Const AgeCritRange          As String = "U3:V7"     'assume it's in Source Sheet
    Const DestShtName           As String = "Sheet2"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("Team", "No. of items", "Value (AUD)", "No. of items without Comments")
    
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
        Age = .Range(CStr(AgeCritRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    c = UBound(Age, 1) * 4 + 4
    
    ReDim k(1 To UBound(ka, 1), 1 To c)
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & ",'" & SourceShtName & "'!" & AgeCritRange & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, Idx * 4 - 4 + 1) = ka(i, 4)
                k(n, Idx * 4 - 4 + 2) = 1
                If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                k(n, Idx * 4 - 4 + 4) = IIf(Len(ka(i, 5)), 1, 0)
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                For m = 1 To c - 4 Step 4
                    k(t(0), m) = ka(i, 4)
                Next
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If CSng(dic1.Item(ka(i, 2))) Then
                    k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                If Len(ka(i, 5)) Then k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(DestShtName)).Range(CStr(DestRange))
            .EntireRow.NumberFormat = "@"
            m = 0
            For i = 1 To c Step 4
                m = m + 1
                If m = UBound(Age, 1) Then
                    .Offset(, i - 1).Value = ">" & Age(m, 1)
                    .Offset(1, i - 1).Resize(, 4).Value = Hdr
                    Exit For
                Else
                    .Offset(, i - 1).Value = Age(m, 1) & "-" & Age(m + 1, 1) - 1
                End If
                .Offset(1, i - 1).Resize(, 4).Value = Hdr
            Next
            .Offset(2).Resize(n, c).Value = k
        End With
    End If
        
End Sub

Setup an Age range like the following

Age Index
2 1
6 2
30 3
51 4
91 5

HTH

Emmily · Apr 17, 2011

I had a play with the criterias, and managed to set the correct ones, i then ran the code and result shown below.
Don't require the team header under each age criteria, there should only be one team header with all the teams listed underneath this header. If there is no data in team then i would like the code to return 0

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2-5

6-29

30-58

59-89

>90

2

Team

No. of items

Value (AUD)

No. of items without Comments

Team

No. of items

Value (AUD)

No. of items without Comments

Team

No. of items

Value (AUD)

No. of items without Comments

Team

No. of items

Value (AUD)

No. of items without Comments

Team

No. of items

Value (AUD)

No. of items without Comments

3

TRACES

4

129268.5703

4

CMP1

1

11593.6631

1

CMP1

1

5796.831543

1

5

LEHMAN

9

Sheet2

Krishnakumar · Apr 17, 2011

Hi,

may be..

Code:

Sub kTest()
    
    Dim ka, k, i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age As String, dic2 As Object, t()
    Dim Hdr, AgeGroup
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("No. of items", "Value (AUD)", "No. of items without Comments", "Value (AUD)")
    AgeGroup = Array("2-5", "6-29", "30-59", "60-89", ">90")
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    Age = ",{2,1;6,2;30,3;60,4;90,5}"
    
    ReDim k(1 To UBound(ka, 1), 1 To 21)
    
    c = 23 - Range(CStr(SourceDataRange)).Column
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & Age & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, 1) = ka(i, 4)
                If Len(ka(i, 5)) Then
                    k(n, Idx * 4 - 4 + 2) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                Else
                    k(n, Idx * 4 - 4 + 4) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 5) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If Len(ka(i, 5)) Then
                    k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                Else
                    k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 5) = k(t(0), Idx * 4 - 4 + 5) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                End If
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(SourceShtName))
            If c > 0 Then .Cells(1).Resize(, c).EntireColumn.Insert
            .Rows(1).NumberFormat = "@"
            .Rows(2).WrapText = 1
            .Rows(2).Font.Bold = 1
            With .Range(CStr(DestRange))
                m = 1
                For i = 0 To UBound(AgeGroup)
                    .Offset(, m).Value = AgeGroup(i)
                    .Offset(1, m).Resize(, 4).Value = Hdr
                    m = m + 4
                Next
                .Offset(2).Resize(n, 21).Value = k
            End With
        End With
    End If
        
End Sub

HTH

Emmily · Apr 17, 2011

Kris, we are heading in the right direction now.

Right, the code needs to add result for the "No of items without comment" and then its AUD value. So as an example for CMP1, we can see from Data in L:P that there is no comment for one of the CMP1 codes, the amount is GBP 2208.9, so the code needs to do the same conversion as inital process but result would go under the below headers.

<table border="0" cellpadding="0" cellspacing="0" width="576"><col width="218"><col width="358"><tr height="17"> <td class="xl65" style="height: 12.75pt; width: 164pt;" height="17" width="218">No. of items without Comments</td> <td class="xl65" style="width: 268pt;" width="358">Value (AUD)</td> </tr></table>
Also where there is no result i need the code to input 0 rather than leave the cells blank

Krishnakumar said:

Hi,

may be..

Code:

Sub kTest()
    
    Dim ka, k, i As Long, c As Long, n As Long, Idx, m As Long
    Dim dic1 As Object, Fx, Age As String, dic2 As Object, t()
    Dim Hdr, AgeGroup
    
    '// adjust to suit
    Const SourceShtName         As String = "Sheet1"
    Const SourceDataRange       As String = "L:P"
    Const StartRow              As Long = 3
    Const FxDataRange           As String = "R3:S16"
    Const DestRange             As String = "A1"
    '//End
    
    
    Hdr = Array("No. of items", "Value (AUD)", "No. of items without Comments", "Value (AUD)")
    AgeGroup = Array("2-5", "6-29", "30-59", "60-89", ">90")
    
    Set dic1 = CreateObject("scripting.dictionary")
        dic1.comparemode = 1
    Set dic2 = CreateObject("scripting.dictionary")
        dic2.comparemode = 1
        
    With Worksheets(CStr(SourceShtName))
        ka = Intersect(.UsedRange, .Range(CStr(SourceDataRange)))
        Fx = .Range(CStr(FxDataRange))
    End With
    
    For i = 1 To UBound(Fx, 1)
        dic1.Item(Fx(i, 1)) = Fx(i, 2)
    Next
    
    Age = ",{2,1;6,2;30,3;60,4;90,5}"
    
    ReDim k(1 To UBound(ka, 1), 1 To 21)
    
    c = 23 - Range(CStr(SourceDataRange)).Column
    
    For i = StartRow To UBound(ka, 1)
        Idx = Evaluate("=lookup(" & ka(i, 3) & Age & ")")
        If Not IsError(Idx) Then
            If Not dic2.exists(ka(i, 4)) Then
                n = n + 1
                k(n, 1) = ka(i, 4)
                If Len(ka(i, 5)) Then
                    k(n, Idx * 4 - 4 + 2) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 3) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                Else
                    k(n, Idx * 4 - 4 + 4) = 1
                    If CSng(dic1.Item(ka(i, 2))) Then k(n, Idx * 4 - 4 + 5) = CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                End If
                dic2.Add ka(i, 4), Array(n, c)
            Else
                t = dic2.Item(ka(i, 4))
                k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                If Len(ka(i, 5)) Then
                    k(t(0), Idx * 4 - 4 + 2) = k(t(0), Idx * 4 - 4 + 2) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 3) = k(t(0), Idx * 4 - 4 + 3) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                Else
                    k(t(0), Idx * 4 - 4 + 4) = k(t(0), Idx * 4 - 4 + 4) + 1
                    If CSng(dic1.Item(ka(i, 2))) Then
                        k(t(0), Idx * 4 - 4 + 5) = k(t(0), Idx * 4 - 4 + 5) + CSng(ka(i, 1) / dic1.Item(ka(i, 2)))
                    End If
                End If
            End If
        End If
    Next
    
    If n Then
        With Worksheets(CStr(SourceShtName))
            If c > 0 Then .Cells(1).Resize(, c).EntireColumn.Insert
            .Rows(1).NumberFormat = "@"
            .Rows(2).WrapText = 1
            .Rows(2).Font.Bold = 1
            With .Range(CStr(DestRange))
                m = 1
                For i = 0 To UBound(AgeGroup)
                    .Offset(, m).Value = AgeGroup(i)
                    .Offset(1, m).Resize(, 4).Value = Hdr
                    m = m + 4
                Next
                .Offset(2).Resize(n, 21).Value = k
            End With
        End With
    End If
        
End Sub

HTH

Krishnakumar · Apr 17, 2011

Hi Emmily,

... So as an example for CMP1, we can see from Data in L:P that there is no comment for one of the CMP1 codes, the amount is GBP 2208.9, so the code needs to do the same conversion as inital process but result would go under the below headers.

The code does it for you. In the example it falls under the criteria ">=90" not "2-5" as you shown in the screen shot.

Kris

Emmily · Apr 17, 2011

Kris, the code needs to do this. At present you have 2 under "No. of items" where there is only 1 entry for CMP1 that is >=90, and that entry has no comment, so we would reflect as shown below

Excel Workbook

A

R

S

T

U

1

>90

2

No. of items

Value (AUD)

No. of items without Comments

Value (AUD)

4

CMP1

1

5,796.83

1

5,796.83

Sheet1

Krishnakumar said:
Hi Emmily,

The code does it for you. In the example it falls under the criteria ">=90" not "2-5" as you shown in the screen shot.

Kris

VBA help

Emmily

Well-known Member

Excel Facts

Emmily

Well-known Member

Krishnakumar

Well-known Member

Emmily

Well-known Member

Emmily

Well-known Member

Emmily

Well-known Member

Krishnakumar

Well-known Member

Emmily

Well-known Member

Krishnakumar

Well-known Member

Emmily

Well-known Member

Similar threads

Forum statistics

Share this page

VBA help

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock