Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    count value with lookup (2000 sr-1)

    with a macro...

    in GAF sheet column H with data.
    In sheet Corporate, Retail_Poe ecc... have a column G with other data.

    I want count how many value are present in column H of GAF refered column G
    of CORPORATE and store the result of count in a variable named CORPORATE...

    I want count how many value are present in column H of GAF refered column G
    of RETAIL_POE and store the result of count in a variable named RETAIL_POE...

    ecc...

    The rest of value in GAF not finded store in a vaiable OTHER....

    tks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count value with lookup (2000 sr-1)

    How about this?

    Steve

    <pre>Option Explicit
    Sub SalCode()
    Dim Corporate As Long
    Dim Retail_Poe As Long
    Dim Pubb_Amm As Long
    Dim Large_Cor As Long
    Dim Other As Long
    Dim i As Integer
    Dim iSheets As Integer
    Dim x As Long
    Dim rGAF As Range
    Dim rCell As Range
    Dim bNotFound As Boolean
    Dim vSheets As Variant
    Dim rSheets() As Range
    Dim iMatch() As Integer
    Dim AWF As WorksheetFunction

    Set AWF = Application.WorksheetFunction
    vSheets = Array("CORPORATE", "RETAIL_POE", "PUBB__AMM", "LARGE_COR")
    With Worksheets("GAF")
    Set rGAF = .Range(.Range("H2"), .Range("H65536").End(xlUp))
    End With

    iSheets = UBound(vSheets)
    ReDim rSheets(0 To iSheets)
    ReDim iMatch(0 To iSheets)
    For i = 0 To iSheets
    iMatch(i) = 0
    With Worksheets(vSheets(i))
    Set rSheets(i) = .Range(.Range("G2"), .Range("G65536").End(xlUp))
    End With
    Next
    Other = 0
    For Each rCell In rGAF
    bNotFound = True
    If rCell.Value <> "" Then
    For i = 0 To iSheets
    x = 0
    On Error Resume Next
    x = AWF.Match(rCell.Value, rSheets(i), 0)
    On Error GoTo 0
    If x <> 0 Then
    bNotFound = False
    iMatch(i) = iMatch(i) + 1
    End If
    Next
    If bNotFound Then
    Other = Other + 1
    End If
    End If
    Next
    Corporate = iMatch(0)
    Retail_Poe = iMatch(1)
    Pubb_Amm = iMatch(2)
    Large_Cor = iMatch(3)

    For i = 0 To iSheets
    Set rSheets(i) = Nothing
    Next
    Set rCell = Nothing
    Set rGAF = Nothing
    End Sub</pre>


  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count value with lookup (2000 sr-1)

    EXCELLENT CODE!!!

    But only one....

    The macro count space or blank cells in GAF?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: count value with lookup (2000 sr-1)

    Spaces will be counted and put into any variable with spaces in the list. Blanks and nulls are ignored (notice the "If rCell.Value <> "" Then" line) You could add an "Else" to this IF to put any blanks/nulls into "other" (or wherever you want them)

    Steve

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: count value with lookup (2000 sr-1)

    Tks for expalin...
    Sal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •