Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 24

Thread: stacked chart vba, variable number of row

  1. #1

    stacked chart vba, variable number of row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    im trying to generate a vba funktion that analysis my Proccestime for some data extraction.

    I face two obstacles.
    1)
    i need the vba function to return how many rows that have data (Each row represent a patient)

    2)
    I need the vba funktion to create a stacked charted with two series.
    column:
    a; is catagory
    b; is series 1
    c; is series 2

    data starts from frow 6 and.
    The difficult part for my to figure out is how i select to end of row(Meaning the number of patients may varie )

    I sencerely hope its possible. The VBA would ease the proccess alot, scoring some nice point with the employer >-<

    thanks in advance
    Subash Suntharalingam
    Lab TEch
    Glostrup hospital
    Denmark

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    You might get away with the following if the data for the chart is bounded by completely blank rows and columns or the edge of the sheet:
    Code:
    Sub blah()
    Range("A6").Select
    ActiveSheet.Shapes.AddChart (xlColumnStacked)
    End Sub
    Attached Thumbnails Attached Thumbnails Click image for larger version. 

Name:	Capturez.JPG 
Views:	43 
Size:	67.7 KB 
ID:	3937  

  3. #3
    Thanks for the advice!.
    I was looking forward to getting back at work and try this out.
    Unfortunately, the code doesnt generate a chart at all.

    So.. il have to keep looking..
    Thanks anyways

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Supply your worksheet (altered with find/replace if sensitive data).

  5. #5
    Quote Originally Posted by p45cal View Post
    Supply your worksheet (altered with find/replace if sensitive data).
    Ived attached a file with hopefully only non sensitive data.

    the vba code that im currently running is :

    Code:
    Sub AKTUEL()
    '
    ' AKTUEL Makro
    '
    ' Genvejstast:Ctrl+s
     'spalt celler
    '
        Columns("B:B").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(21, 2)), TrailingMinusNumbers:=True
        Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 2), Array(4, 2)), TrailingMinusNumbers:=True
            
            'tilføj filter og skul kolonner
            
        Rows("1:1").Select
        Selection.AutoFilter
        Columns("B:C").Select
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 2
        Columns("F:F").Select
        Selection.EntireColumn.Hidden = True
        Columns("J:J").Select
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        Columns("L:P").Select
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 17
        Columns("L:Q").Select
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        Columns("L:AA").Select
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 1
        Columns("E:E").Select
        Selection.EntireColumn.Hidden = True
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        
        ' Filter efter buffey
        
        ActiveSheet.Range("$A$1:$AC$302").AutoFilter Field:=4, Criteria1:= _
            "Buffycoat-EDTA"
        ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
            ("G1:G302"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        ActiveWindow.ScrollColumn = 2
        
        ' formater måned dag og år for udtræk
        
        Columns("H:K").Select
        Selection.NumberFormat = "m/d/yyyy h:mm"
        Columns("AB:AB").Select
        
        'indsæt kolonner og format time : mm
        
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Columns("AB:AD").Select
        Selection.NumberFormat = "h:mm"
        
        ' udregn tidsforskel og navngiv celler
        
        
        Range("AB5").Select
        ActiveCell.FormulaR1C1 = "=RC[-19]-RC[-20]"
        Range("AC5").Select
        ActiveCell.FormulaR1C1 = "=RC[-18]-RC[-20]"
        Range("AD5").Select
        ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-2]"
        Range("AB1").Select
        ActiveCell.FormulaR1C1 = "Udt. Til modt."
        Range("AC1").Select
        ActiveCell.FormulaR1C1 = "Modt til i frys"
        Range("AD1").Select
        ActiveCell.FormulaR1C1 = "Total tid"
        Range("AB5").Select
        
        ' Træk formular ned til end xlup.
        
        Range("AB5:AD5").AutoFill Destination:=Range("AB5:AD" & Cells(Rows.Count, "k").End(xlUp).Row)
        Columns("G:K").Select
        Selection.EntireColumn.Hidden = True
        Range("A1").Select
        ActiveCell.CurrentRegion.Select
        
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Copy
        Sheets.Add
        ActiveSheet.Paste
        
        Columns("A:A").ColumnWidth = 20.56
        Columns("A:A").ColumnWidth = 23.22
        Columns("B:B").ColumnWidth = 10.56
        Columns("C:C").ColumnWidth = 13.11
        Columns("D:D").ColumnWidth = 12.78
        Columns("E:E").ColumnWidth = 13.11
        Columns("B:B").Select
        Application.CutCopyMode = False
        Selection.Cut
        Columns("J:J").Select
        Selection.Insert Shift:=xlToRight
        Selection.ColumnWidth = 18
        Range("B22").Select
        
            Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Antal rækker"
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "Periode"
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "procent pr række"
        Range("A4").Select
        
        ' sorter
        
          Rows("5:5").Select
        Selection.AutoFilter
        ActiveWorkbook.Worksheets("Ark1").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Ark1").AutoFilter.Sort.SortFields.Add Key:=Range( _
            "D5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Ark1").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        'akkummuleret
        
           ActiveCell.FormulaR1C1 = "=100"
        Range("B3").Select
        ActiveCell.FormulaR1C1 = "=100/R[-1]C"
        Columns("E:E").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        
        
        Range("E6").Select
        ActiveCell.FormulaR1C1 = "=R[-3]C[-3]"
        Range("E7").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C+R[-4]C[-3]"
        Range("E8").Select
        
        
        Columns("E:E").Select
        Selection.NumberFormat = "0.00"
        Range("E7").Select
        ActiveCell.FormulaR1C1 = "=R[-1]C+R3C2"
        Range("E8").Select
        Range("E5").Select
        ActiveCell.FormulaR1C1 = "Akkummuleret procent"
        Range("E6").Select
        Columns("E:E").ColumnWidth = 20.78
        Columns("E:E").Select
        Selection.NumberFormat = "0"
        
        ' filldown Makro
        
        Range("D7").Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp)).Select
        Selection.filldown
        
        ' format af procent
        
        Range("B3").Select
        Selection.NumberFormat = "0.00"
    End Sub
    Attached Files Attached Files
    Last edited by p45cal; 2015-09-22 at 12:23 PM. Reason: added code tags

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    2 questions:
    1. You have the line beginning ActiveWorkbook.Worksheets("Ark1").AutoFil.....
    Is there a line missing in the code that renames the freshly created sheet to Ark1 or is this an existing sheet elsewhere?

    2. You have the line ActiveCell.FormulaR1C1 = "=100"
    Which is the active cell at the time? (When I run it it is cell A5 which contains a header which I doubt you want changing to a formula.)

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Finally, what data do you want plotting? A5:C5 all the way down?

  8. #8
    When i run the action, it creates a new sheet named "ark1"
    at this point
    "
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste"
    The newly generated sheet is named ark1. isnt it just the language difference in excel setting?

    Regarding the second question.
    You are absolutely right. That wasnt right.

    i was trying to buil data to generate a chart of processtime as a function of percentage.
    Hence my need to have the numbers of patients/rows. (which is a second chart that i didnt post about in this thread)






    Quote Originally Posted by p45cal View Post
    2 questions:
    1. You have the line beginning ActiveWorkbook.Worksheets("Ark1").AutoFil.....
    Is there a line missing in the code that renames the freshly created sheet to Ark1 or is this an existing sheet elsewhere?

    2. You have the line ActiveCell.FormulaR1C1 = "=100"
    Which is the active cell at the time? (When I run it it is cell A5 which contains a header which I doubt you want changing to a formula.)

  9. #9
    exactly.
    row 5 is series name tho..

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by subazz View Post
    The newly generated sheet is named ark1. isnt it just the language difference in excel setting?
    Yes, you're quite right, I should have guessed that Ark is Sheet in Danish/Swedish/Norwegian! Sorry!


    Quote Originally Posted by subazz View Post
    Regarding the second question.
    You are absolutely right. That wasnt right.
    i was trying to buil data to generate a chart of processtime as a function of percentage.
    Hence my need to have the numbers of patients/rows. (which is a second chart that i didnt post about in this thread)
    So what should I do here?
    Last edited by p45cal; 2015-09-22 at 10:56 PM.

Page 1 of 3 1 2 3 LastLast

Tags for this Thread

Posting Permissions

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