process proteomics.vba

Sub Process_0_1_genuniquepeptides()
'
' Counts # of unique peptides per reference.
'

'

    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
    Range("E4").Activate
    Selection.AutoFilter
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:="="
    Range("D:D,L:L").Select
    Range("L1").Activate
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Columns("A:A").EntireColumn.AutoFit
    
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "a"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[-2],LEN(RC[-2])-2)"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & LastRow)
    Range("C2:C" & LastRow).Select
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "b"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],LEN(RC[-1])-2)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & LastRow)
    Range("D2:D" & LastRow).Select
    Range("A1:D" & LastRow).Select
    Range("D2").Activate
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Selection.Replace What:="@", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="]", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="[", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="~*", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveSheet.Name = "unique peptides"
    
    Range("C:C,A:A").Select
    Range("A1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Sequence"
    Columns("A:B").Select
    ActiveSheet.Range("$A$1:$B$" & LastRow).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlNo
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-2],RC[-2])"
    
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Selection.AutoFill Destination:=Range("C2:C" & LastRow)
    Range("C2:C" & LastRow).Select
    Range("A:A,C:C").Select
    Range("C1").Activate
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Range("B1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Unique peptides"
    Columns("A:B").Select
    LastRow = ActiveSheet.UsedRange.Rows.Count
    ActiveSheet.Range("$A$1:$B$" & LastRow).RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
    ActiveSheet.Name = "uniquecount"
    
End Sub


Sub Process_1_1()
'
' Process_1_1 Macro
' Initial processing of raw data for cleanup and get log2 ratios


    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    
    ' Get rid of garbage first entries
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.AutoFilter
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
        Clear
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
        Add Key:=Range("A1:A" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=1, Criteria1:="="
    
    ' Copy to another scratch worksheet
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    
    acell = Cells.Find(What:="Enabled", After:=Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
    bcell = Cells.Find(What:="O18_RATIO_COUNT", After:=Cells(1, 1), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
        
    bcell = bcell - 1
    
    ' Get rid of ENABLED=false
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.Range("c1").End(xlToRight).Column
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=Range( _
        Cells(1, acell), Cells(LastRow, acell)), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.ActiveSheet.Sort
        .SetRange Range("a1", ActiveSheet.Cells(LastRow, LastCol))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Selection.AutoFilter
        
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=acell, Criteria1:="TRUE"
    
    ' Copy to another scratch worksheet
    Cells.Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    
        
    ' Sort by log values
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Selection.AutoFilter
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
        Clear
    
    ActiveWorkbook.ActiveSheet.AutoFilter.Sort.SortFields. _
        Add Key:=Range(Cells(1, bcell), Cells(LastRow, bcell)), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Log2 ratios
    bcell = bcell + 1
    Cells(1, bcell).Select
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Range("A1").Select
    ActiveCell.FormulaR1C1 = "log2(Ratio)"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=LOG(RC[-1],2)"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow)
    
    ' Prompt and filter?
    intMsg = Application.InputBox("Enter log2 filtering cutoff (e.g. 4 filters log values outside -4 or +4), or 0 to disable ", _
        "Enter number", 3.33, Type:=1) ' 3.33 = 0.1 to 10
    bcell = bcell - 1 ' Correct for first column
    If intMsg > 0 Then
        ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=bcell, Criteria1:=">=-" & intMsg, _
        Operator:=xlAnd, Criteria2:="<=" & intMsg
    End If
    
    ' Cleanup
    
    Columns("A:A").Select
    Selection.Delete Shift:=xlLeft


End Sub

Sub Process_1_1_sharedpeptides()
'
' Sub-Macro to remove shared peptides
' A shared peptide is defined by a reference with a ! or a / in the name.


' Copy existing data to a new sheet
LastRow = ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    
ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

Cells.Find(What:="Reference", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    ' Shared peptide column
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "Shared?"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=OR(NOT(ISERROR(FIND(""/"",RC[1]))),NOT(ISERROR(FIND(""!"",RC[1]))))"
    ActiveCell.Select
    rcell = Cells.Find(What:="Shared?", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
    ' Filter
    
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
    Selection.AutoFilter Field:=rcell, Criteria1:="FALSE"
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    
End Sub

Sub Process_2_1()
'
' Process_2_1 Macro
' Process to generate individual stats after log2 filtering
'

'
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Cells.Find(What:="log2(Ratio)", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    ' Label all cells
    ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.Select
    ActiveCell.FormulaR1C1 = "NORM log2(Ratio)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "#Quantifications"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "MEAN NORM log2(ratios)"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "NORM log2(ratios)^2"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "stdev"
    ActiveCell.Offset(0, 1).Range("A1").Select
    
    ' NORM
    ActiveCell.Offset(1, -5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]-AVERAGE(R2C[-1]:R" & LastRow & "C[-1])"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
    ' Quantifications
    ActiveCell.Offset(0, 1).Range("A1").Select
    rcell = Cells.Find(What:="Reference", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Column
    curcell = ActiveCell.Column
    totalcell = rcell - curcell
    'MsgBox ("curcell - rcell = " & curcell & "-" & rcell & "=" & totalcell)
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[" & totalcell & "],RC[" & totalcell & "])"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
    ' MEAN NORM
    ActiveCell.Offset(0, 1).Range("A1").Select
    totalcell = totalcell - 1
    ActiveCell.FormulaR1C1 = "=AVERAGEIF(C[" & totalcell & "],RC[" & totalcell & "],C[-2])"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
    ' NORM2
    ActiveCell.Offset(0, 1).Range("A1").Select
    totalcell = totalcell - 1
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-3]"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
    ' STDEV
    ActiveCell.Offset(0, 1).Range("A1").Select
    totalcell = totalcell - 1
    ActiveCell.FormulaR1C1 = "=SQRT((SUMIF(C[" & totalcell & "],RC[" & totalcell & "],C[-1]) - RC[-3]*RC[-2]*RC[-2])/(RC[-3]-1))"
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A" & LastRow - 1)
    
End Sub

Sub Process_3_1()
'
' Process_3_1 Macro
' Process to generate fold changes and sort for IPA after log2 filtering
'
' Required column format:
'
' Reference - identifier - UniProt ID will be generated
' #Quantifications - n
' MEAN NORM log2(Ratio) - Sum(xi-mean)
' NORM log2(Ratio)^2
' stdev


'
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Range("A1:D" & LastRow).Select
    Range("A1:D" & LastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
        Header:=xlYes
    'Selection.AutoFilter
    'ActiveSheet.Range("$A$1:$D$23810").AutoFilter Field:=2, Criteria1:=">=3", _
    '    Operator:=xlAnd
    ' Range("A1:D" & LastRow).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    
    LastRow = ActiveSheet.UsedRange.Rows.Count
    
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Select
    Selection.Copy
    Columns("A:A").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "UniProt ID"
    Columns("A:A").Select
    Selection.Replace What:="_RAT*", Replacement:="_RAT", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("A:A").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    
    ActiveCell.FormulaR1C1 = "MEAN FOLD CHANGE"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,2^RC[2],-1/2^RC[2])"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & LastRow)
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "T test"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=ABS(RC[-2]/(RC[-1]/SQRT(RC[-3])))"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & LastRow)
    
    
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "p value"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "BH p value"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Bonferroni p value"
    Columns("H:J").Select
    Columns("H:J").EntireColumn.AutoFit
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "=TDIST(RC[-1],RC[-4]-1,2)"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & LastRow)
    
    ' Merge values, replace
    
    Columns("A:H").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:H").Select
    Selection.Replace What:="#DIV/0!", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Selection.Replace What:="#NUM!", Replacement:="0", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    ' BH p value
    
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]*(COUNT(C[-2]))>1,1,RC[-2]*(COUNT(C[-2])))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & LastRow)
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*(COUNT(C[-1]))/RANK(RC[-1],C[-1],1)"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I" & LastRow)
    
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range( _
        "I2:I" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.Sort
        .SetRange Range("A1:J" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    ' Merge, paste
    
    Columns("I:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
End Sub


Sub Process_4_1()
'
' Makes filtered data from final and recalculates p values

'
LastRow = ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    
ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
Selection.AutoFilter
    
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=6, Criteria1:=">0"
    
    ' Prompt quantitation?
    intMsg = Application.InputBox("Enter # quantitations (>=3). P values will be calculated after entries < this number are removed. ", _
        "Enter number", 3, Type:=1)
    If intMsg > 0 Then
        ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=4, Criteria1:=">=" & intMsg
    End If
    
    ' Copy to new sheet
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    LastRow = ActiveSheet.UsedRange.Rows.Count
    LastCol = ActiveSheet.Range("a1").End(xlToRight).Column
    
    ' Recalculate and paste BH and Bonf. p values
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]*(COUNT(C[-2]))>1,1,RC[-2]*(COUNT(C[-2])))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & LastRow)
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]*(COUNT(C[-1]))/RANK(RC[-1],C[-1],1)>1,1,RC[-1]*(COUNT(C[-1]))/RANK(RC[-1],C[-1],1))"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I" & LastRow)
    
    ' Merge, paste
    
    Columns("I:J").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ' Autofilter start
    
    ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).Select
    Selection.AutoFilter
    
    ' Prompt fold change?
    intMsg = Application.InputBox("Enter fold change minimum (absolute value) ", _
        "Enter number", 1.5, Type:=1)
    If intMsg > 0 Then
        ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=3, Criteria1:=">=" & intMsg, _
        Operator:=xlOr, Criteria2:="<=-" & intMsg
    End If
    
    ' Prompt BH P-value?
    intMsg = Application.InputBox("Enter BH p-value maximum ", _
        "Enter number", 0.05, Type:=1)
    If intMsg > 0 Then
        ActiveSheet.Range("a1", ActiveSheet.Cells(LastRow, LastCol)).AutoFilter Field:=9, Criteria1:="<=" & intMsg
    End If
    
    '
    
End Sub

Sub Conv_Formula_To_Values()
    For Each wSh In ActiveWorkbook.Worksheets
         If wSh.AutoFilterMode Then wSh.AutoFilterMode = False
    Next wSh
    Worksheets.Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    ActiveSheet.Select
    Application.CutCopyMode = False
End Sub