ErrorBars for defined range

fefenouil

New Member
Joined
May 25, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

So I am making progress with my first ever vba code but I am apparently stuck once again with a problem I don't find the solution over different blogs/forums.
My macro allow me to select data and then run some layout on a new sheet with some calculations and then show me the data in a graph. Up to this point everything is running (somehow) smoothly, but then I went my graph to have error bars and this is where I am stuck.
My error bars are associated with series and are thus defined as range. From what I have understood the ErrorBars "function" works with R1C1 addresses and I cannot figure out how to use my range. I have tried using the .Address to convert in R1C1 format, to convert the range before using it in R1C1, to use the "=" & method but none of them worked for me. I guess that I am using those wrongly so I am asking for your guys help on this one.
Thanks.

VBA Code:
Sub DatAnalysis()

'add new working sheet
Dim SheetName As String
SheetName = Application.InputBox(Prompt:="Sheet name", Type:=2)
Sheets.Add(Before:=Worksheets("raw")).Name = SheetName
Worksheets(SheetName).Activate
With Sheets(SheetName)

'average and stdev naming row
    Range("A17").Value = "average"
    Range("A18").Value = "stdev"

'Graph prep
    Dim oChartObj As ChartObject
    Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=300, Left:=50, Width:=500, Height:=250)
    Dim oChart As Chart
    Set oChart = ActiveSheet.ChartObjects(1).Chart
  
'X axis
    oChart.Axes(xlCategory).HasTitle = True
    oChart.Axes(xlCategory).AxisTitle.Caption = "Peptide concentration"

'Y axis
    oChart.Axes(xlValue).HasTitle = True
    oChart.Axes(xlValue).AxisTitle.Caption = "Relative cell viability (%)"

'negative value transfer
    Dim negval As Range
    Worksheets("raw").Activate
    Set negval = Application.InputBox(Prompt:="Pick the neg values", Type:=8)
    negval.Copy
    Worksheets(SheetName).Activate
    Range("A2").PasteSpecial Paste:=xlPasteValues
    Range("A1").Value = "neg"
    Range("A6") = Application.WorksheetFunction.Average(Range("A2:A4"))
    
'Background
    Dim bckgrnd As Range
    Worksheets("raw").Activate
    Set bckgrnd = Application.InputBox(Prompt:="Pick the bckgrnd values", Type:=8)
    bckgrnd.Copy
    Worksheets(SheetName).Activate
    Range("B2").PasteSpecial Paste:=xlPasteValues
    Range("B1").Value = "bckgrnd"
    Range("B6") = Application.WorksheetFunction.Average(Range("B2:B4"))
    
'Set up for loop
    Dim SerieValue As Range
    Dim SerieName As String
    Dim Condi As Boolean
    Dim CondiConc As Boolean
    Dim SerieLentgh As Integer
    Dim i As Integer
    Dim j As Integer
    Dim aver As Range
    Dim sd As Range
    i = 1
    j = 1
    Condi = False
    
        Do Until Condi = True
    
'selection of series values
            Worksheets("raw").Activate
            Set SerieValue = Application.InputBox(Prompt:="Pick Serie or empty cell if no more serie", Type:=8)
            If Not IsEmpty(SerieValue) Then Condi = False
            If IsEmpty(SerieValue) Then Condi = True
            SerieLength = SerieValue.Rows.Count
       
'copying serie
            Worksheets(SheetName).Activate
            SerieName = Application.InputBox(Prompt:="Please input serie name.", Type:=2)
            SerieValue.Copy Destination:=Cells(9, i + 1)
'name serie
            Cells(7, i + 1).Value = SerieName
            Range(Cells(7, i + 1), Cells(7, i + SerieLength)).Select
                Selection.Merge
                Selection.HorizontalAlignment = xlCenter
'transform data
            Range(Cells(13, i + 1), Cells(15, i + SerieLength)).FormulaR1C1 = "=(((R[-4]C[0]-R6C2)/R6C1)*100)"
'average and stdev
        Set aver = Range(Cells(17, i + 1), Cells(17, i + SerieLength))
        aver.FormulaR1C1 = "=AVERAGE(R[-4]C[0]:R[-2]C[0])"
        Set sd = Range(Cells(18, i + 1), Cells(18, i + SerieLength))
        sd.FormulaR1C1 = "=STDEV(R[-5]C[0]:R[-3]C[0])"
     
'Transfer to graph
        With oChartObj.Chart
            .ChartType = xlColumnClustered
            .SeriesCollection.NewSeries
            .SeriesCollection(j).Name = SerieName
            .SeriesCollection(j).Values = aver
            .SeriesCollection(j).HasErrorBars = True
            .SeriesCollection(j).ErrorBars.Select
            .SeriesCollection(j).ErrorBars Direction:=xlY, Include:= _
            xlBoth, Type:=xlCustom, Amount:="='New'!" & sd.Address(, , xlR1C1), MinusValues:="='New'!" & sd.Address(, , xlR1C1)
            
'Y minimum value
            .Axes(xlValue).MinimumScale = 0

        End With
        
'Looping increment
        i = i + SerieLength
        j = j + 1
    
        Loop
        
End With
End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

fefenouil

New Member
Joined
May 25, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

So I am making progress with my first ever vba code but I am apparently stuck once again with a problem I don't find the solution over different blogs/forums.
My macro allow me to select data and then run some layout on a new sheet with some calculations and then show me the data in a graph. Up to this point everything is running (somehow) smoothly, but then I went my graph to have error bars and this is where I am stuck.
My error bars are associated with series and are thus defined as range. From what I have understood the ErrorBars "function" works with R1C1 addresses and I cannot figure out how to use my range. I have tried using the .Address to convert in R1C1 format, to convert the range before using it in R1C1, to use the "=" & method but none of them worked for me. I guess that I am using those wrongly so I am asking for your guys help on this one.
Thanks.

VBA Code:
Sub DatAnalysis()

'add new working sheet
Dim SheetName As String
SheetName = Application.InputBox(Prompt:="Sheet name", Type:=2)
Sheets.Add(Before:=Worksheets("raw")).Name = SheetName
Worksheets(SheetName).Activate
With Sheets(SheetName)

'average and stdev naming row
    Range("A17").Value = "average"
    Range("A18").Value = "stdev"

'Graph prep
    Dim oChartObj As ChartObject
    Set oChartObj = ActiveSheet.ChartObjects.Add(Top:=300, Left:=50, Width:=500, Height:=250)
    Dim oChart As Chart
    Set oChart = ActiveSheet.ChartObjects(1).Chart
 
'X axis
    oChart.Axes(xlCategory).HasTitle = True
    oChart.Axes(xlCategory).AxisTitle.Caption = "Peptide concentration"

'Y axis
    oChart.Axes(xlValue).HasTitle = True
    oChart.Axes(xlValue).AxisTitle.Caption = "Relative cell viability (%)"

'negative value transfer
    Dim negval As Range
    Worksheets("raw").Activate
    Set negval = Application.InputBox(Prompt:="Pick the neg values", Type:=8)
    negval.Copy
    Worksheets(SheetName).Activate
    Range("A2").PasteSpecial Paste:=xlPasteValues
    Range("A1").Value = "neg"
    Range("A6") = Application.WorksheetFunction.Average(Range("A2:A4"))
   
'Background
    Dim bckgrnd As Range
    Worksheets("raw").Activate
    Set bckgrnd = Application.InputBox(Prompt:="Pick the bckgrnd values", Type:=8)
    bckgrnd.Copy
    Worksheets(SheetName).Activate
    Range("B2").PasteSpecial Paste:=xlPasteValues
    Range("B1").Value = "bckgrnd"
    Range("B6") = Application.WorksheetFunction.Average(Range("B2:B4"))
   
'Set up for loop
    Dim SerieValue As Range
    Dim SerieName As String
    Dim Condi As Boolean
    Dim CondiConc As Boolean
    Dim SerieLentgh As Integer
    Dim i As Integer
    Dim j As Integer
    Dim aver As Range
    Dim sd As Range
    i = 1
    j = 1
    Condi = False
   
        Do Until Condi = True
   
'selection of series values
            Worksheets("raw").Activate
            Set SerieValue = Application.InputBox(Prompt:="Pick Serie or empty cell if no more serie", Type:=8)
            If Not IsEmpty(SerieValue) Then Condi = False
            If IsEmpty(SerieValue) Then Condi = True
            SerieLength = SerieValue.Rows.Count
      
'copying serie
            Worksheets(SheetName).Activate
            SerieName = Application.InputBox(Prompt:="Please input serie name.", Type:=2)
            SerieValue.Copy Destination:=Cells(9, i + 1)
'name serie
            Cells(7, i + 1).Value = SerieName
            Range(Cells(7, i + 1), Cells(7, i + SerieLength)).Select
                Selection.Merge
                Selection.HorizontalAlignment = xlCenter
'transform data
            Range(Cells(13, i + 1), Cells(15, i + SerieLength)).FormulaR1C1 = "=(((R[-4]C[0]-R6C2)/R6C1)*100)"
'average and stdev
        Set aver = Range(Cells(17, i + 1), Cells(17, i + SerieLength))
        aver.FormulaR1C1 = "=AVERAGE(R[-4]C[0]:R[-2]C[0])"
        Set sd = Range(Cells(18, i + 1), Cells(18, i + SerieLength))
        sd.FormulaR1C1 = "=STDEV(R[-5]C[0]:R[-3]C[0])"
    
'Transfer to graph
        With oChartObj.Chart
            .ChartType = xlColumnClustered
            .SeriesCollection.NewSeries
            .SeriesCollection(j).Name = SerieName
            .SeriesCollection(j).Values = aver
            .SeriesCollection(j).HasErrorBars = True
            .SeriesCollection(j).ErrorBars.Select
            .SeriesCollection(j).ErrorBars Direction:=xlY, Include:= _
            xlBoth, Type:=xlCustom, Amount:="='New'!" & sd.Address(, , xlR1C1), MinusValues:="='New'!" & sd.Address(, , xlR1C1)
           
'Y minimum value
            .Axes(xlValue).MinimumScale = 0

        End With
       
'Looping increment
        i = i + SerieLength
        j = j + 1
   
        Loop
       
End With
End Sub
I haven't uploaded the right code actually, the destination sheet was named "New" before but code didn't worked either

VBA Code:
            .SeriesCollection(j).ErrorBars Direction:=xlY, Include:= _
            xlBoth, Type:=xlCustom, Amount:="='New'!" & sd.Address(, , xlR1C1), MinusValues:="='New'!" & sd.Address(, , xlR1C1)
 

fefenouil

New Member
Joined
May 25, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
Problem solved by Setting de novo sd after calculation

VBA Code:
            Set sd = Range(Cells(18, i), Cells(18, SerieLength + i - 1))
                sd.FormulaR1C1 = "=STDEV(R[-5]C[0]:R[-3]C[0])"
                Set sd = Range(Cells(18, i), Cells(18, SerieLength + i - 1))
'Transfer to graph
       With oChartObj.Chart
            .ChartType = xlColumnClustered
            .SeriesCollection.NewSeries
            .SeriesCollection(j).Name = SerieName
            .SeriesCollection(j).Values = aver
            .SeriesCollection(j).ErrorBar Direction:=xlY, Include:=xlBoth, Type:=xlErrorBarTypeCustom, Amount:=sd, MinusValues:=sd.Select
            .SetElement (msoElementErrorBarNone)
            .SeriesCollection(j).HasErrorBars = True
 
Solution

Forum statistics

Threads
1,176,106
Messages
5,901,416
Members
434,891
Latest member
mahmoud Alhamaky

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top