Thread: charts and VBA

I have a chart for which the data table is updated automatically and that I need to chart in a "different" way to the conventional excel charts.

I kind of understand what is required in terms of logic and VBA process but can't transfer the grey matter to the project edito correctly. I have provided an example of what I have to work with and what I want at the end..... hopefully somebody can help???

Simon

For clarity using the example attached what I want to do is the following;

if B5 = "1" then put the contents of A5 into D23 and format it. If B5 <> 1 then move to next row repeating the process until B24. If D23 is already populated put the contents into the next available column.

Then I want to repeat the loop asking the same question if B5 = "2" and so on upto a maximum of 20.

I don't understand the logic you've applied to arrive at your manual results.

Not much.

If somebody is offered an option of 1 to 20, what will they choose and how many people will do so?

Options for what -- "Pick a number between 1 and 20?"

What's in the first three columns of the chart?

Pretend we have no idea what you're talking about (a safe bet) and try again.

ok Shg sorry, I'm clearing making you a little mad at me, I'll try again...

1. What the figures relate to bears no real consequence to the solution.
2. Column A can be a list of 1-20 questions regarding anything, or stock numbers or catalogue numbers. The constant is that it will always be numbered 1-20.
3. Column B is the result, whether it be sales, purchases, how many people answered yes to a question etc etc....

Ahhhh..... i think I understand the dilemma....

4. Column C is an expected range of answers from 1 - 20 (sorry I wasn't thinking that it duplicated column A). I have put 20 as the maximum purely as I could envisage the variable getting that high although it isn't in column B in the example.

So...to give an example

If I'm wondering how good the answers to Q.7 (column A) are in relation to Q.10(column A). The answers are in Column B i.e. 5 and 8, means nothing really.... So I am trying to reproduce it in a table which means a whole lot more to me.

Therefore to order answers sequentially in ascending order left to right I know immediately where to look without further analysis. (for info only; I also invariably have a mean figure which by formatting the mean number in column C draws my eye immediately to what is above and what is below expectations. I use the info in other ways but neither of us have all night so best leave those out).

So, in a nut shell what I am trying to achieve is the following;

Firstly, clear D4:N23 of values, colours and formatting.
Secondly, starting at B5; does the value = 1? if yes enter the value of A5 into D23; if yes or no move onto B6 and continue down to B24 in the same way.
Thirdly, if there is a value already in (for example) D23, then put the figure into E23 as in the example.
Finally, colour cells that have values in them and give them a border.

I think it sounds a bit better like that doesn't it?

Simon

ps ... sorry I hate reading these things back...what I'm thinking never translates correctly.... the process is not just looking for values of "1". when the loop is finnished on looking for a value of 1 in B5:B24 I obviously want to repeat the loop but looking for a value of 2 etc up to an expected maximum of 20ish.....

I have attached my latest attempt at it!

OK Having failed to communicate the problem properly on a separate thread, let’s try again….

Essentially I have 2 columns of information; A & B.

A – is just a list of question numbers from 1 – 20
B – is a list of scores/answers to the questions from column A.

I expect the scores/answers to any single question to number no more than 15 (I have changed this from the original thread to avoid confusion).

I have started to create a manual example table which historically for me has been the most helpful. The spreadsheet attached also includes my latest failed attempt at VBA which can be ignored as it appears desperately wrong!

Column C is numbered 1-15 and if you like is my “Y” axis, which is as far as it bears resemblance to a standard chart/graph.

Columns D to N will be populated by the result from column B as per the example. The results are then also colour coded (which I have included purely so you can understand why I can’t use a standard bar chart).

I need to write VBA to put the contents of the table into the corresponding chart as per the example.

??

Simon

ps can somebdy explain the meaning of the blanks to my old thread? (bespoke charts & VBA) as it appears broke or something?

No need to start a new thread - threads merged.

hi opsman
for trial [s]1[/s] 2

try...

Code:

 
Sub test()
Dim a, b(), i As Long, n As Long, e, x
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 2)
        a = .Value
    End With
    ReDim b(1 To UBound(a, 1), 1 To 20)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For i = 2 To UBound(a, 1)
            n = n + 1
            If Not .exists(a(i, 2)) Then
                b(n, 1) = a(i, 2)
                .Add a(i, 2), n
            End If
            b(.Item(a(i, 2)), 2) = b(.Item(a(i, 2)), 2) & _
                                   IIf(b(.Item(a(i, 2)), 2) <> "", ",", "") & a(i, 1)
        Next
        For i = 1 To n
            x = 1
            For Each e In Split(b(i, 2), ",")
                Cells(Choose(b(i, 1), 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1) + 3, 3 + x).Value = e
                x = x + 1
            Next
        Next
    End With
    End Sub

Shg thanks for your patience!!

Pike - I'm eeeeelated at your code, yet dissappointed at my lack of knowledge....I'll try not to dwell on it as it's becoming a habit...

I'm trying to learn as I go due to an employer who in the current climate refuses (in fact "ever" regardless of climate and a widely advertised policy to the contrary) to send me on a course, any chance you could expand upon how you achieved that in more simple terms..?

If that's a bit too cheeky I'll understand!

Either way many many thanks to you.

Simon

Hi opsman

The easiest way to learn is to use the VB help or just high light a string in the module and press F1 for a code example.Also Ask questions and try to solve posts. Its very friendly here and it is known the real way to hon your skills is to participate. Dont worry its alway interesting to see how other solve the same problem as there are many way to skin a Banana.( Poor Bananas)

The code it self is a mix of code copied from posts from people such as AndyPope, shg, mikerickerson and royUK which I have collected then dissected.The first part is using the VBscripting dictonarary
the second is a loop to split and distribute the dictonarary
your problem was complicated by the layout of the data and could have been simply solved but rearranging the spread sheet.

The only book I have is VB for dummies by John Walkenback
A good place to start