Results 1 to 5 of 5

Thread: Moving data in Excel via vba

  1. #1
    Join Date
    Jan 2011
    Posts
    26

    Moving data in Excel via vba

    I am looking for a way to manage data labels in an automated charting procedure I am writing. The chart is a waterfall chart (I believe) and I would like to be able to loop through each data series/column to find the height of the total column and then set the label for the total column to sit x points above it. There is a second issue I need help with. As the chart displays components of return for stocks and bonds, I'd like to place a line in between the last stock and the first bond shown. Is there a way that I can place this line dynamically (charts will like have different numbers of stocks and bonds - as will a particular chart over time). I think there is an add-in someone mentioned I could try downloading but I would like to avoid that if possible.

  2. #2
    Join Date
    Aug 2011
    Posts
    540

    Re: Moving data in Excel via vba

    I think what you are looking for is what I use to control the chart from Access when I export data. Change the source data, for series 1 to what ever to something similar to your legend as it is easier to work with. Running a macro would give you something similar to my code and there is a few options of where to place the label. I have had a little problem to display both the legend or identification line and the value in the same block and that is why there is two sections and it is display as a line chart. With Source data you can change the series to a name.
    Code:
    With ActiveChart
    With ActiveChart.Axes(xlValue)
    .MinimumScale = 30
    .MaximumScale = 170
    .MinorUnit = 1
    ' .MajorUnitIsAuto = True
    '.Crosses = xlCustom
    .CrossesAt = 30
    .ReversePlotOrder = False
    '.ScaleType = xlLinear
    
    End With
    
    With .SeriesCollection("Upper Control Limit").Points(19)
    .HasDataLabel = True
    .DataLabel.Text = " Upper Control Limit ="
    .DataLabel.Font.Size = 7
    .DataLabel.Font.Italic = True
    .DataLabel.HorizontalAlignment = xlRight
    .DataLabel.VerticalAlignment = xlTop
    .DataLabel.Position = xlLabelPositionAbove
    .DataLabel.Orientation = xlHorizontal
    End With
    
    With .SeriesCollection("Upper Control Limit").Points(24)
    .HasDataLabel = True
    ' .DataLabel.Text = "Upper Control Limit = 2.3"
    .DataLabel.Font.Size = 7
    .DataLabel.Font.Italic = True
    .DataLabel.HorizontalAlignment = xlRight
    .DataLabel.VerticalAlignment = xlTop
    .DataLabel.Position = xlLabelPositionAbove
    .DataLabel.Orientation = xlHorizontal
    End With

  3. #3
    Join Date
    Aug 2011
    Posts
    695

    Re: Moving data in Excel via vba

    I follow a methodology which is a little more complicated in the chart, but much less so in VBA (in fact, it requires NO VBA). For each set of labels I need, I add a line chart (or XY chart) series to the chart, with X and Y values selected to precisely locate the label with respect to the floating columns. Various formulas adjust these X and Y as needed to move the labels to align with the columns. The added series are formatted to be hidden (no line, no markers). Then I use one of these free Excel add-ins to apply the appropriate data labels to the points. Once applied, I never need to readjust them, because the data label text is linked to the cells in the worksheet that contain the text for the labels.

  4. #4
    Join Date
    Aug 2011
    Posts
    566

    Re: Moving data in Excel via vba

    Thanks for the response. I do have one follow-up question: is there a way to set the label values to show different values than the ones that would normally be automatically picked up in the chart? For example, instead of showing a label value of "2" could I tie it to a cell in the worksheet that has a value "4" (or "Eggs" for that matter). Thanks again. I am a little concerned about unsigned the Add-ins given my IT department may be a tough group to allow external programs being brought in (sorry!).

  5. #5
    Join Date
    Jul 2011
    Posts
    623

    Re: Moving data in Excel via vba

    You can link a data label, as well as chart titles, to cells. Here is an explanation of how to do it manually for the chart title but the principle is the same for data labels. Just click the data labels once to select and the click the individual data label in order to be able to link to a cell.

Similar Threads

  1. Replies: 4
    Last Post: 04-01-2014, 10:47 AM
  2. Tera copy very slow on moving 5GB of data on Windows XP
    By ronaldobrazil in forum Windows Software
    Replies: 4
    Last Post: 19-07-2013, 10:56 AM
  3. Replies: 2
    Last Post: 17-02-2012, 05:36 PM
  4. Replies: 6
    Last Post: 17-05-2011, 10:00 PM
  5. Moving from FAT32 to NTFS without losing data
    By Trickster in forum Hardware Peripherals
    Replies: 6
    Last Post: 14-10-2008, 07:57 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,722,807.92870 seconds with 17 queries