Solved

Excel VBA chart array

Posted on 2010-11-08
2
627 Views
Last Modified: 2012-05-10
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
Comment
2 Comments
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 34085786
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
 

Author Closing Comment

by:lancegallagher_expertsexchange
ID: 34087615
Thanks very much, this works great!!

Sorry about the slow response, the network went down
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question