none
Search for test no. RRS feed

  • Question

  • Hi All, 

    I have done a current code that run from a fixed column instead of the test number. Every column has different test number. How to do a search for the test no. and it will run automatically. I need this for 2 purposes  1) to plot histogram 2) to plot scatter plot. The histogram i only need 1 column of data but for scatter plot i need 2-4 column of data.

    Sub Macro456() histogram
    '
    ' Macro1 Macro
    ' Macro recorded 2012-06-29 by sgg90282
    '
    
    '
        Dim Sht As Worksheet
        Dim LastRow As Long
        Dim ChartLastRow As Long
        
        With Worksheets("Raw_data")
        .Columns("L:L").Copy
        End With
        
        Set Sht = Sheets.Add
        
        With Sht
            .Name = "Sheet1"
            .Paste
         End With
         
        LastRow = Sht.Range("A65536").End(xlUp).Row
        
        Application.Run "ATPVBAEN.XLA!Histogram", ActiveSheet.Range("A7:A" & LastRow), ActiveSheet.Range("B7"), , , False, False _
            , False
        Range("C1").FormulaR1C1 = "Step"
        Range("D1").FormulaR1C1 = "1.24E-03"
        Range("C2").FormulaR1C1 = "mean"
        Range("D2").FormulaR1C1 = "=AVERAGE(R[5]C[-3]:R[1128]C[-3])"
        Range("C3").FormulaR1C1 = "StDev"
        Range("D3").FormulaR1C1 = "=STDEV(R[4]C[-3]:R[1127]C[-3])"
        Range("F1").FormulaR1C1 = "FS"
        Range("F2").FormulaR1C1 = "7.46E-01"
        Range("G2").FormulaR1C1 = "6.338-01"
        Range("H1").FormulaR1C1 = "MC"
        Range("H2").FormulaR1C1 = "6.25E-01"
        Range("I2").FormulaR1C1 = "7.57E-01"
        Range("J1").FormulaR1C1 = "PCM"
        Range("J2").FormulaR1C1 = "6.70E-01"
        Range("K2").FormulaR1C1 = "7.90E-01"
        Range("J7").FormulaR1C1 = "Frequency"
        Range("F2:K2").NumberFormat = "0.000E+00"
        Range("C8:C41").Copy
        Range("J51").Select
        ActiveSheet.Paste
        
        Range("F4").FormulaR1C1 = "Steps for X"
        Range("G4").FormulaR1C1 = "=R9C2-R8C2"
        Range("F7").FormulaR1C1 = "X"
        Range("G7").FormulaR1C1 = "Density"
        Range("H7").FormulaR1C1 = "D*N*Step"
        Range("I7").FormulaR1C1 = "Value"
        
        Range("F4").FormulaR1C1 = "Steps for X"
        Range("G4").FormulaR1C1 = "=R9C2-R8C2"
        Range("F7").FormulaR1C1 = "X"
        Range("G7").FormulaR1C1 = "Density"
        Range("H7").FormulaR1C1 = "D*N*Step"
        Range("I7").FormulaR1C1 = "Value"
        
        Range("B8:B40").Copy
        Range("F8").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("F8").FormulaR1C1 = "=R8C2"
       
        
        Range("G8").FormulaR1C1 = "=NORMDIST(RC[-1],R2C4,R3C4,FALSE)"
        Range("H8").FormulaR1C1 = "=RC[-1]*1124*R1C4"
        Range("L167").FormulaR1C1 = "=R8C2-R4C7"
        Range("L166").FormulaR1C1 = "=R[1]C-R4C7"
        Range("L166").AutoFill Destination:=Range("L125:L166"), Type:=xlFillDefault
        Range("L125:L167").Copy
        Range("F8").Insert Shift:=xlDown
        Range("L125:L167").ClearContents
    
        Range("F84").FormulaR1C1 = "=R[-1]C+R4C7"
        Range("F85").FormulaR1C1 = "=R[-1]C+R4C7"
        Range("F85").AutoFill Destination:=Range("F85:F278"), Type:=xlFillDefault
        Range("G8").FormulaR1C1 = "=NORMDIST(RC[-1],R2C4,R3C4,FALSE)"
        Range("G8").AutoFill Destination:=Range("G8:G278")
        Range("H8").AutoFill Destination:=Range("H8:H278")
        Range("F8:F278").NumberFormat = "0.000E+00"
    
        Range("I10").FormulaR1C1 = "180"
        Range("I24").FormulaR1C1 = "180"
        Range("I83").FormulaR1C1 = "180"
        Range("I204").FormulaR1C1 = "180"
        Range("I223").FormulaR1C1 = "180"
        Range("I275").FormulaR1C1 = "180"
    
        ChartLastRow = Sht.Range("F65536").End(xlUp).Row
    
        Range("F7:J" & ChartLastRow).Select
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F7:J" & ChartLastRow), PlotBy:=xlColumns
        ActiveChart.Location Where:=xlLocationAsNewSheet
        ActiveChart.SeriesCollection(3).ChartType = xlArea
        ActiveChart.SeriesCollection(1).Delete
        ActiveChart.SeriesCollection(1).Delete
    ''  ActiveChart.SeriesCollection(1).XValues = "=Sheet1!R8C6:R286C6"
    ''  ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R8C6:R286C6"
    ''  ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R8C6:R286C6"
        ActiveChart.SeriesCollection(1).XValues = Sheets("Sheet1").Range("F8:F" & ChartLastRow)
        ActiveChart.SeriesCollection(2).XValues = Sheets("Sheet1").Range("F8:F" & ChartLastRow)
        ActiveChart.SeriesCollection(3).XValues = Sheets("Sheet1").Range("F8:F" & ChartLastRow)
        ActiveChart.Axes(xlValue).MaximumScale = 180
    
        ActiveChart.Legend.LegendEntries(1).Delete
        ActiveChart.Legend.LegendEntries(1).Delete
    
        ActiveChart.Axes(xlValue).TickLabels.Font.Size = 9
        ActiveChart.Axes(xlCategory).TickLabels.Font.Size = 8
    
        ActiveChart.SeriesCollection(2).Border.LineStyle = xlNone
        ActiveChart.SeriesCollection(2).Points(17).Interior.ColorIndex = 3
        ActiveChart.SeriesCollection(2).Points(197).Interior.ColorIndex = 3
        ActiveChart.SeriesCollection(2).Points(3).Interior.ColorIndex = 4
        ActiveChart.SeriesCollection(2).Points(216).Interior.ColorIndex = 4
        ActiveChart.SeriesCollection(2).Points(76).Interior.ColorIndex = 42
        ActiveChart.SeriesCollection(2).Points(268).Interior.ColorIndex = 42
    
    End Sub
    
    Sub Macro716777()
    '
    ' Macro9011004 Macro scatterplot
    ' Macro recorded 2012-07-23 by sgg90282
    '
    
    '
        Range("X:X,AD:AD").Select
        Range("X2").Activate
        Selection.Copy
        Sheets("Raw_data").Select
        Sheets.Add
        ActiveSheet.Paste
        Application.CutCopyMode = False
    
        Range("E6").FormulaR1C1 = "FS Specs"
        Range("E7").FormulaR1C1 = "5.6953E-01"
        Range("F7").FormulaR1C1 = "5.6953E-01"
        Range("E8").FormulaR1C1 = "5.6953E-01"
        Range("F8").FormulaR1C1 = "4.5121E-01"
        Range("E9").FormulaR1C1 = "4.5121E-01"
        Range("F9").FormulaR1C1 = "4.5121E-01"
        Range("E10").FormulaR1C1 = "4.5121E-01"
        Range("F10").FormulaR1C1 = "5.6953E-01"
        Range("E11").FormulaR1C1 = "5.6953E-01"
        Range("F11").FormulaR1C1 = "5.6953E-01"
        Range("G7").FormulaR1C1 = "(x1,y1)"
        Range("G8").FormulaR1C1 = "(x1,y2)"
        Range("G9").FormulaR1C1 = "(x2,y2)"
        Range("G10").FormulaR1C1 = "(x2,y1)"
        Range("G11").FormulaR1C1 = "(x1,y1)"
        
        Range("I6").FormulaR1C1 = "MC Specs"
        Range("I7").FormulaR1C1 = "4.3963E-01"
        Range("J7").FormulaR1C1 = "4.3482E-01"
        Range("I8").FormulaR1C1 = "4.3963E-01"
        Range("J8").FormulaR1C1 = "5.6950E-01"
        Range("I9").FormulaR1C1 = "5.7918E-01"
        Range("J9").FormulaR1C1 = "5.6950E-01"
        Range("I10").FormulaR1C1 = "5.7918E-01"
        Range("J10").FormulaR1C1 = "4.3482E-01"
        Range("I11").FormulaR1C1 = "4.3963E-01"
        Range("J11").FormulaR1C1 = "4.3482E-01"
        Range("K7").FormulaR1C1 = "(x1,y1)"
        Range("K8").FormulaR1C1 = "(x1,y2)"
        Range("K9").FormulaR1C1 = "(x2,y2)"
        Range("K10").FormulaR1C1 = "(x2,y1)"
        Range("K11").FormulaR1C1 = "(x1,y1)"
        
        Application.CommandBars("Forms").Visible = True
        ActiveSheet.Buttons.Add(336.75, 153, 192, 90).Select
        Selection.OnAction = "Button1_Click"
        Selection.Characters.Text = "Change Coordinates"
        
        Dim sht As Worksheet, LastRow As Long
        Set sht = Sheets("Sheet1")
        LastRow = sht.Range("A65536").End(xlUp).Row
        
        Range("A6:B" & LastRow).Select
        Charts.Add
        ActiveChart.ChartType = xlXYScatter
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A6:B" & LastRow), PlotBy _
            :=xlColumns
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(2).XValues = "=Sheet1!R7C5:R11C5"
        ActiveChart.SeriesCollection(2).Values = "=Sheet1!R7C6:R11C6"
        ActiveChart.SeriesCollection(2).Name = "=Sheet1!R6C5"
        ActiveChart.SeriesCollection(3).XValues = "=Sheet1!R7C9:R11C9"
        ActiveChart.SeriesCollection(3).Values = "=Sheet1!R7C10:R11C10"
        ActiveChart.SeriesCollection(3).Name = "=Sheet1!R6C9"
        ActiveChart.Location Where:=xlLocationAsNewSheet
      
        ActiveChart.SeriesCollection(3).Select
        With Selection.Border
            .ColorIndex = 4
            .Weight = xlHairline
            .LineStyle = xlContinuous
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 5
            End With
            
        ActiveChart.SeriesCollection(2).Select
        With Selection.Border
            .ColorIndex = 3
            .Weight = xlHairline
            .LineStyle = xlContinuous
        End With
        With Selection
            .MarkerBackgroundColorIndex = xlAutomatic
            .MarkerForegroundColorIndex = xlAutomatic
            .MarkerStyle = xlNone
            .Smooth = False
            .MarkerSize = 5
        End With
        
    '    ActiveChart.SeriesCollection(1).Select
    '    With Selection.Border
    '        .Weight = xlHairline
    '        .LineStyle = xlNone
    '    End With
    '    With Selection
    '        .MarkerBackgroundColorIndex = xlAutomatic
    '        .MarkerForegroundColorIndex = xlAutomatic
    '        .MarkerStyle = xlDiamond
    '        .Smooth = False
    '        .MarkerSize = 2
    '        .Shadow = False
    '    End With
    
    End Sub
    



    • Edited by ngkj Thursday, August 2, 2012 8:14 AM
    Thursday, August 2, 2012 8:11 AM