Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 647
  • Last Modified:

Excel VBA chart array

Hello Experts,

I need some help to populate two similar chart matrix using VBA. I'm not too sure how approach this. I need to loop within a recordset and build a string to populate a matrix, which depends upon two criteria. This should make more sense if you look at the attached example.

Any help and suggestions are very much appreciated
Employee-Rating.xls
0
lancegallagher_expertsexchange
Asked:
lancegallagher_expertsexchange
1 Solution
 
krishnakrkcCommented:
Hi,

Unmerge the cells on Ratings sheet.

and try this macro.

Kris
Sub kTest()
    Dim k, M(1 To 3, 1 To 3), F(1 To 3, 1 To 3)
    Dim i   As Long, c As Long, r As Long
    
    k = Sheets("Data").UsedRange.Resize(, 3)
    
    For i = 2 To UBound(k, 1)
        Select Case k(i, 3)
            Case Is <= 3
                r = 1: c = k(i, 3)
            Case Is <= 6
                r = 2: c = k(i, 3) - 3
            Case Else
                r = 3: c = k(i, 3) - 6
        End Select
        If k(i, 2) = "Male" Then
            M(r, c) = IIf(Len(M(r, c)), M(r, c) & ", " & k(i, 1), k(i, 1))
        Else
            F(r, c) = IIf(Len(F(r, c)), F(r, c) & ", " & k(i, 1), k(i, 1))
        End If
    Next
    [c3:e5].Value = M
    [h3:j5].Value = F
End Sub

Open in new window

0
 
lancegallagher_expertsexchangeAuthor Commented:
Thanks very much, this works great!!

Sorry about the slow response, the network went down
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now