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.
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
