Results 1 to 7 of 7

Thread: Macro ([bash])

  1. #1
    New Lounger
    Join Date
    Aug 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro ([bash])

    I am trying to create a macro that creates a bar chart for each of about 200 people. Each bar chart will have 3 bars in it. For example the first chart data will come from (b2,c2,d2) respectively. the second from (b3, c3, d3) respectively. and so on. The code that I have now is below. It runs but won't stop and I think it is only doing th first chart over and over. Any help would be appreciated.
    Thanks
    jonathan

    Dim FILEDONE As String
    ' OFFSETDOWN = 1
    ' OFFSETLEFT = 0
    FILEDONE = "GO"
    Do Until FILEDONE = ""
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Emp Data").Range("K10")
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "='Emp Data'!R2C6"
    ActiveChart.SeriesCollection(1).Name = "=""3%"""
    ActiveChart.SeriesCollection(2).Values = "='Emp Data'!R2C14"
    ActiveChart.SeriesCollection(2).Name = "=""4%"""
    ActiveChart.SeriesCollection(3).Values = "='Emp Data'!R2C16"
    ActiveChart.SeriesCollection(3).Name = "=""bump it"""
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Emp Data"
    With ActiveChart
    .HasAxis(xlCategory, xlPrimary) = False
    .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    Loop

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro ([bash])

    Welcome to Woody's Lounge!

    Your code loops until FILEDONE = "", but since FILEDONE is never set to "", the loop never ends. You need to specify a stop condition that actually works.
    You mention that the source data will be from columns B, C and D, but the code seems to get it from columns F, N and P, and always from row 2. The role of K10 is unclear. Can you explain what you actually want?
    All charts are overlaid on each other. It's probably better to use the ChartObjects.Add on the Emp Data sheet; the Add method lets you specify the position.

    It would help if you could attach a small sample workbook with dummy data.

  3. #3
    New Lounger
    Join Date
    Aug 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro ([bash])

    I will attach some dummy data to this post. I just used b and c because I thought it would be easier to understand. The columns actually are F, N and P. K10 I think is where the cursor was when I clicked the chart icon. The code I posted I got from just recording the macro doing the first chart and then added a loop. Well kind of added a loop. The macro in the file is called Chart.
    Thanks
    Jonathan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro ([bash])

    Try this version. It loops until the last populated cell in column A is encountered, and positions each chart below the previous one.

    Sub Chart1()
    Dim i As Long
    Dim n As Long
    Dim wsh As Worksheet
    Dim cho As ChartObject
    Dim cht As Chart
    Set wsh = Worksheets("Emp Data")
    n = wsh.Range("A65536").End(xlUp).Row
    For i = 2 To n
    Set cho = wsh.ChartObjects.Add(Top:=160 * i - 310, Left:=10, _
    Height:=150, Width:=300)
    Set cht = cho.Chart
    cht.ChartType = xlColumnClustered
    cht.SeriesCollection.Add Source:=wsh.Range("F" & i)
    cht.SeriesCollection.Add Source:=wsh.Range("N" & i)
    cht.SeriesCollection.Add Source:=wsh.Range("P" & i)
    cht.SeriesCollection(1).Name = "3%"
    cht.SeriesCollection(2).Name = "4%"
    cht.SeriesCollection(3).Name = "bump it"
    cht.HasAxis(xlCategory) = False
    Next i
    End Sub

  5. #5
    New Lounger
    Join Date
    Aug 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro ([bash])

    I dont think I explained it very well in the previous post, so here is what I am looking for. eventually what I want it to do is create the chart for each person and then be able to merge that chart into a word document. the word document is a mail merged file. Each persons chart will be in a different record in the word document. This to me seems like it would be really hard to do. If I could get it to create the charts I would just transfer them over manually. Hopefully this makes some sense.

    Thanks
    Jonathan

  6. #6
    New Lounger
    Join Date
    Aug 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro ([bash])

    Thank you Hans this worked great. I do have one more question. How do I get the value of each bar in the chart to appear at the top of each chart bar? I tried looking through the intelisense but I dont see anything that fits that

    Thanks
    Jonathan

  7. #7
    New Lounger
    Join Date
    Aug 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro ([bash])

    Hans I want to thank you for your help. I ended up figuring out the value thing.

    Thanks again
    Jonathan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •