Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 649
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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