Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subsequence of characters
Hi,
I have used the VBA code bellow in order to calculate the molecular mass of proteisn and peptides. It calculates the molecular mass of the characters sequence in A:A and display the corresponding value in B:B. In addition to the mass of a entire characters sequence, now I would like to search for regions within the sequences for which some rules are determined. Therefore, I would like to put in a cell a mass value, in another cell the standard deviation that could be satifected and as result I would like to see the sequence in which such a mass value was detected, the deviation value (positive or negative), the region of the full sequence in which such value was detected, the character immediately before the subsequence and the character after the subsequence. Finally, I would like that such subsequences list could be ordered by minor deviation from the searched value until the higher deviation. Do you have some suggestion how could I do that using a VBA code? I'm putting an exaple bellow and the VBA code I have used to calculate the molecular mass of a full sequence. Thanks in advance, Luciano Example: The sequences are (cells A1:A3): FLSLIPHAINAVSAIAKHN FLSLIPHAINAVSAIAHHF SLIPHAINAVSAIAKHF Sequences names are respectively (B1:B3): PS1 PS2 PS3 I would like to search the mass 323.32 (cell D1) The standard deviation is 10 (cell D2) Thus, I would like to receive as results for specified data (Columns E:K): Seq Region Subseq Mass value Sta. deviation Before After PS1 [15 - 17] IAK 330.22669 (+) 6.90669 A H PS3 [13 - 15] IAK 330.22669 (+) 6.90669 A H PS1 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS1 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS2 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS2 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS3 [1 - 3] SLI 331.21071 (+) 7.89071 - P : Sub MM() ' ' Protweight Macro ' Macro recorded 20/10/2005 by Luciano Paulino da Silva ' iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = 1 To iLastRow For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "A": MW = MW + 71.03711 Case "C": MW = MW + 103.00919 Case "D": MW = MW + 115.02694 Case "E": MW = MW + 129.04259 Case "F": MW = MW + 147.06841 Case "G": MW = MW + 57.02146 Case "H": MW = MW + 137.05891 Case "I": MW = MW + 113.08406 Case "K": MW = MW + 128.09496 Case "L": MW = MW + 113.08406 Case "M": MW = MW + 131.04049 Case "N": MW = MW + 114.04293 Case "P": MW = MW + 97.05276 Case "Q": MW = MW + 128.05858 Case "R": MW = MW + 156.10111 Case "S": MW = MW + 87.03203 Case "T": MW = MW + 101.04768 Case "V": MW = MW + 99.06841 Case "W": MW = MW + 186.07931 Case "Y": MW = MW + 163.06333 y = y + 1.00782 Case Else: Z = Z + 1.00782 End Select Next j If MW 18 Then Cells(i, "C").Value = MW End If MW = 0 Next i Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subsequence of characters
-
paulinoluciano escreveu: Hi, I have used the VBA code bellow in order to calculate the molecular mass of proteisn and peptides. It calculates the molecular mass of the characters sequence in A:A and display the corresponding value in B:B. In addition to the mass of a entire characters sequence, now I would like to search for regions within the sequences for which some rules are determined. Therefore, I would like to put in a cell a mass value, in another cell the standard deviation that could be satifected and as result I would like to see the sequence in which such a mass value was detected, the deviation value (positive or negative), the region of the full sequence in which such value was detected, the character immediately before the subsequence and the character after the subsequence. Finally, I would like that such subsequences list could be ordered by minor deviation from the searched value until the higher deviation. Do you have some suggestion how could I do that using a VBA code? I'm putting an exaple bellow and the VBA code I have used to calculate the molecular mass of a full sequence. Thanks in advance, Luciano Example: The sequences are (cells A1:A3): FLSLIPHAINAVSAIAKHN FLSLIPHAINAVSAIAHHF SLIPHAINAVSAIAKHF Sequences names are respectively (B1:B3): PS1 PS2 PS3 I would like to search the mass 323.32 (cell D1) The standard deviation is 10 (cell D2) Thus, I would like to receive as results for specified data (Columns E:K): Seq Region Subseq Mass value Sta. deviation Before After PS1 [15 - 17] IAK 330.22669 (+) 6.90669 A H PS3 [13 - 15] IAK 330.22669 (+) 6.90669 A H PS1 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS1 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS2 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS2 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS3 [1 - 3] SLI 331.21071 (+) 7.89071 - P : Sub MM() ' ' Protweight Macro ' Macro recorded 20/10/2005 by Luciano Paulino da Silva ' iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = 1 To iLastRow For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "A": MW = MW + 71.03711 Case "C": MW = MW + 103.00919 Case "D": MW = MW + 115.02694 Case "E": MW = MW + 129.04259 Case "F": MW = MW + 147.06841 Case "G": MW = MW + 57.02146 Case "H": MW = MW + 137.05891 Case "I": MW = MW + 113.08406 Case "K": MW = MW + 128.09496 Case "L": MW = MW + 113.08406 Case "M": MW = MW + 131.04049 Case "N": MW = MW + 114.04293 Case "P": MW = MW + 97.05276 Case "Q": MW = MW + 128.05858 Case "R": MW = MW + 156.10111 Case "S": MW = MW + 87.03203 Case "T": MW = MW + 101.04768 Case "V": MW = MW + 99.06841 Case "W": MW = MW + 186.07931 Case "Y": MW = MW + 163.06333 y = y + 1.00782 Case Else: Z = Z + 1.00782 End Select Next j If MW 18 Then Cells(i, "C").Value = MW End If MW = 0 Next i Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subsequence of characters
-
paulinoluciano escreveu: - paulinoluciano escreveu: Hi, I have used the VBA code bellow in order to calculate the molecular mass of proteisn and peptides. It calculates the molecular mass of the characters sequence in A:A and display the corresponding value in B:B. In addition to the mass of a entire characters sequence, now I would like to search for regions within the sequences for which some rules are determined. Therefore, I would like to put in a cell a mass value, in another cell the standard deviation that could be satifected and as result I would like to see the sequence in which such a mass value was detected, the deviation value (positive or negative), the region of the full sequence in which such value was detected, the character immediately before the subsequence and the character after the subsequence. Finally, I would like that such subsequences list could be ordered by minor deviation from the searched value until the higher deviation. Do you have some suggestion how could I do that using a VBA code? I'm putting an exaple bellow and the VBA code I have used to calculate the molecular mass of a full sequence. Thanks in advance, Luciano Example: The sequences are (cells A1:A3): FLSLIPHAINAVSAIAKHN FLSLIPHAINAVSAIAHHF SLIPHAINAVSAIAKHF Sequences names are respectively (B1:B3): PS1 PS2 PS3 I would like to search the mass 323.32 (cell D1) The standard deviation is 10 (cell D2) Thus, I would like to receive as results for specified data (Columns E:K): Seq Region Subseq Mass value Sta. deviation Before After PS1 [15 - 17] IAK 330.22669 (+) 6.90669 A H PS3 [13 - 15] IAK 330.22669 (+) 6.90669 A H PS1 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS1 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS2 [2 - 4] LSL 331.21071 (+) 7.89071 F I PS2 [3 - 5] SLI 331.21071 (+) 7.89071 L P PS3 [1 - 3] SLI 331.21071 (+) 7.89071 - P : Sub MM() ' ' Protweight Macro ' Macro recorded 20/10/2005 by Luciano Paulino da Silva ' iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = 1 To iLastRow For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "A": MW = MW + 71.03711 Case "C": MW = MW + 103.00919 Case "D": MW = MW + 115.02694 Case "E": MW = MW + 129.04259 Case "F": MW = MW + 147.06841 Case "G": MW = MW + 57.02146 Case "H": MW = MW + 137.05891 Case "I": MW = MW + 113.08406 Case "K": MW = MW + 128.09496 Case "L": MW = MW + 113.08406 Case "M": MW = MW + 131.04049 Case "N": MW = MW + 114.04293 Case "P": MW = MW + 97.05276 Case "Q": MW = MW + 128.05858 Case "R": MW = MW + 156.10111 Case "S": MW = MW + 87.03203 Case "T": MW = MW + 101.04768 Case "V": MW = MW + 99.06841 Case "W": MW = MW + 186.07931 Case "Y": MW = MW + 163.06333 y = y + 1.00782 Case Else: Z = Z + 1.00782 End Select Next j If MW 18 Then Cells(i, "C").Value = MW End If MW = 0 Next i Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subsequence of characters
-
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subsequence of characters
Don't you have a different thread active in a different newsgroup?
And didn't they suggest that you had answers to your question when you posted a couple of months ago? It's probably best to stay in one thread (where you're getting responses) and explain why those other suggestions didn't work for you. paulinoluciano wrote: - -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I control the length of characters a code will return? | Excel Discussion (Misc queries) | |||
Formula to replace invalid filename characters | Excel Worksheet Functions | |||
Remove varying amounts of space characters | Excel Discussion (Misc queries) | |||
Help - Can't directly input Japanese characters into cell | Excel Discussion (Misc queries) | |||
Max number of characters in a cell | Excel Discussion (Misc queries) |