Add custom error bars from data adjacent to series data using VBA

I have a chart that contains several series of data that are organized under the same columns. For example, Series1 = (C3:C9,G3:G9), Series2 = (C10:C16,G10:G16), etc. I want to use a macro that automatically adds custom error bars whose positive and negative ranges are one column to the right of the series Y-data, e.g. H3:H9 for Series1. Below is my attempt at the code but I have difficulty defining the error bar range (see Line 6). I would like the macro to add error bars to all series in the chart. Anybody have suggestions?

Please Login or Register  to view this content.

Andy,

"txt" is a reference to the Series. The code [ActiveChart.SeriesCollection(i).Formula] gives "=SERIES("Series1",Sheet1!$C$3:$C$9,$G$3:$G$9). I was using this to get ($G$3:$G$9) for the Y-values, then ideally use OFFSET to get the corresponding error values one row to the right.

Andy,

Here is the example workbook. I have four series whose names are in Col B, x-values in Col C, y-values in Col F and error values in Col G. Ideally, the macro should capture the references to the y-values (e.g. Sheet1!$F$3:$F$9) and offset it to the error values (Sheet1!$G$3:$G$9) and use that reference to create custom error bars.

guys,

looks like I got it figured out. one of the issues what the ErrorBar Type, which I changed to "xlErrorBarTypeCustom" (refer to link). I kept srsYval as the entire reference. I added a line to turn off x-error bars. I also cleaned up some of the code. Let me know if you have any suggestions on improving the code.
Thanks for the help!

Please Login or Register  to view this content.