[SOLVED] charts and VBA

opsman · #1 06-21-2009, 10:26 AM

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.

shg · #2 06-21-2009, 02:00 PM

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

opsman · #3 06-21-2009, 03:23 PM

ok sorry about that let me try and explain further....

The table figures on the left are not "logically" related to the results, really that's where my problem in programming it seems to be...

How do I describe this...(could take a while)

If somebody is offered an option of 1 to 20, what will they choose and how many people will do so? The total unfortunately will always change depending on the ammount of people asked, but if the majority of the results fall into a similar a group of choices I want to identify that easily. The figures will change regularly as new figures come in and I want to avoid doing it long hand all of the time.

The constants are the table itself on the left, I will always offer 20 options.

I have tried all manner of normal excel graphs to try and replicate my example, but none of them tell me what I want with such clarity.

I don't think it's pivotable if that's your line of thought. I think I'm going to do it long hand so to speak.

Since posting I have revisited again and seem to be able to create a long hand way of asking what is adjacent to "1" and putting it where I want it. I am stuck with what happens where the cell is already filled with a result. My way cannot be the easiest as it will take pages and pages of script??

I also seem to be getting blocking errors which as a beginner have not seen before.

Does this help at all??

Simon

shg · #4 06-21-2009, 03:28 PM

Not much.

Quote:

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.

opsman · #5 06-21-2009, 04:42 PM

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!

opsman · #6 06-21-2009, 06:45 PM

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?

opsman · #7 06-21-2009, 06:48 PM

I am reposting as "charts and VBA", hopefully my description and examples are more easily understood and followed having spent some time thinking about it.

Thanks for your help. If you are still thinking about it perhaps you can pick it up on the new thread?

Simon

shg · #8 06-21-2009, 10:20 PM

No need to start a new thread - threads merged.

pike · #9 06-21-2009, 11:02 PM

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

opsman · #10 06-22-2009, 02:53 PM

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

pike · #11 06-22-2009, 05:08 PM

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.

opsman · #12 06-22-2009, 05:51 PM

OK, I'll try and take your advice and spend more time solving things!

Many thanks once again.

Simon

pike · #13 06-22-2009, 06:21 PM

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

opsman · #14 06-25-2009, 04:36 PM

I have just started reading Mastering VBA programmin in Excel 2003 by Steven Hansen....perhaps I need more time reading and less time playing for a bit!!

Simon

....or a combination of the two!

pike · #15 06-25-2009, 04:52 PM

or a good project to work on and solve

Welcome to the Excel Forum

Forum Statistics