Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
"I would like to use the VBA codes bellow that were writen by Gerry
Shaw in order to calculate some properties of proteins and DNA. However how is this script such results can be done only for the selected cell (example A1) and the results are displayed as a message box. I would like to perform such operations for all cells in range (A:A) that contains data and that the results could be displayed on the left (B:B) of each one of these cells. Someone can help me with this procedure? Have I to use loop? Code 1: Sub IEP() ' ' IEP Macro ' Macro created 07/01/98 by Gerry Shaw ' X = Len(Selection.Text) If X = 0 Then MsgBox ("No selection made") For YY= 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "C" C = C + 1 Case "D" D = D + 1 Case "E" E = E + 1 Case "H" H = H + 1 Case "K" K = K + 1 Case "R" R = R + 1 Case "Y" Y = Y + 1 End Select Next YY ' Define array of nine elements containing the pKa values of amino acids, N and C-termini Dim pK(8) As Double pK(0) = 0.000446 ' C-term pK(1) = 0.0000851 ' Glu pK(2) = 0.000126 ' Asp pK(3) = 0.000000000661 ' Lys pK(4) = 0.00000000102 ' Arg pK(5) = 0.00000000447 ' Cys pK(6) = 0.000000912 ' His pK(7) = 0.000000000776 ' Tyr pK(8) = 0.000000000166 ' N-term For pH = 2 To 12 Step 0.1 ' calculates HH, the proton concentration HH = Exp(-pH * Log(10)) ' positive charge is function of proton conc, number of K, R and H and N-terminus pcharge = HH * K / (HH + pK(3)) + HH * R / (HH + pK(4)) + HH * H / (HH + pK(6)) + HH / (HH + pK(8)) ' negative charge is function of proton conc, number of Tyr, Cys, Glu, Asp and C-terminus ncharge = Y * (1 - (HH / (HH + pK(7)))) + C * (1 - (HH / (HH + pK(5)))) + 1 - (HH / (HH + pK(1))) + E * (1 - (HH / (HH + pK(1)))) + D * (1 - (HH / (HH + pK(2)))) ' exits for loop when pcharge is less than ncharge If (pcharge <= ncharge) Then Exit For Next pH MsgBox ("pKa = " & Format(pH, "fixed")) End Sub Code 2: The second macro, Nucweight, determines the molecular weight of a mouse selected DNA sequence. Sequence for this version must be uppercase text. To make it read lowercase, which is what you get with most sequence databases nowadays, change the Case commands; for example change Case "A" to Case "a" and make corresponding changes to the other Case commands. Sub Nucweight() ' ' Nucweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' This version for DNA sequences: for RNA MW for A = 329.2, U = 306.1, G = 345.2, C= 305.2 ' X = Len(Selection.Text) For YY = 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "A" MW = MW + 313.2 Case "T" MW = MW + 304.2 Case "G" MW = MW + 329.2 Case "C" MW = MW + 289.2 Case Else Z = Z + 1 End Select Next YY MW = MW + 18 If (MW 18) Then MsgBox ("Selection includes " & X - Z & " bases, " & "Molecular Weight= " & MW & " Daltons") If (MW = 18) Then MsgBox ("No sequence selected") End Sub Code 3: This macro determines counts the number of amino acids and determines the molecular weight of a mouse selected protein sequence Sub Protweight() ' ' Protweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' X = Len(Selection.Text) For YY = 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "A" MW = MW + 71.09 Case "C" MW = MW + 103.15 Case "D" MW = MW + 115.1 Case "E" MW = MW + 129.13 Case "F" MW = MW + 147.19 Case "G" MW = MW + 57.07 Case "H" MW = MW + 137.16 Case "I" MW = MW + 113.17 Case "K" MW = MW + 128.19 Case "L" MW = MW + 113.17 Case "M" MW = MW + 131.31 Case "N" MW = MW + 114.12 Case "P" MW = MW + 97.13 Case "Q" MW = MW + 128.15 Case "R" MW = MW + 156.2 Case "S" MW = MW + 87.09 Case "T" MW = MW + 101.12 Case "V" MW = MW + 99.15 Case "W" MW = MW + 186.23 Case "Y" MW = MW + 163.19 Y = Y + 1 Case Else Z = Z + 1 End Select Next YY MW = MW + 18 If MW 18 Then MsgBox ("Selection includes " & X - Z & " amino acids, Molecular Weight= " & Format(MW, "fixed") & " Daltons") Else MsgBox ("No Sequence Selected") End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
1.
Sub IEP() ' ' IEP Macro ' Macro created 07/01/98 by Gerry Shaw ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To iLastRow For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "C": C = C + 1 Case "D": D = D + 1 Case "E": E = E + 1 Case "H": H = H + 1 Case "K": K = K + 1 Case "R": R = R + 1 Case "Y": Y = Y + 1 End Select Next j ' Define array of nine elements containing the pKa values of amino acids , N And C - termini Dim pK(8) As Double pK(0) = 0.000446 ' C-term pK(1) = 0.0000851 ' Glu pK(2) = 0.000126 ' Asp pK(3) = 0.000000000661 ' Lys pK(4) = 0.00000000102 ' Arg pK(5) = 0.00000000447 ' Cys pK(6) = 0.000000912 ' His pK(7) = 0.000000000776 ' Tyr pK(8) = 0.000000000166 ' N-term For pH = 2 To 12 Step 0.1 ' calculates HH, the proton concentration HH = Exp(-pH * Log(10)) ' positive charge is function of proton conc, ' number of K, R and H and N -terminus pcharge = HH * K / (HH + pK(3)) + HH * R / _ (HH + pK(4)) + HH * H / (HH + pK(6)) + _ HH / (HH + pK(8)) ' negative charge is function of proton conc, ' number of Tyr, Cys, Glu,Asp and C-terminus ncharge = Y * (1 - (HH / (HH + pK(7)))) + _ C * (1 - (HH / (HH + pK(5)))) + 1 - _ (HH / (HH + pK(1))) + E * (1 - (HH / (HH + pK(1)))) + _ D * (1 - (HH / (HH + pK(2)))) ' exits for loop when pcharge is less than ncharge If (pcharge <= ncharge) Then Exit For Next pH Cells(i, "B").Value = "pKa = " & Format(pH, "fixed") Next i End Sub 2. Sub Nucweight() ' ' Nucweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' This version for DNA sequences: for RNA MW for A = 329.2, ' U = 306.1, G = 345.2, C= 305.2 ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row 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 + 313.2 Case "T": MW = MW + 304.2 Case "G": MW = MW + 329.2 Case "C": MW = MW + 289.2 Case Else: Z = Z + 1 End Select Next j MW = MW + 18 If MW 18 Then Cells(i, "B").Value = "Selection includes " & X - Z & " bases, " & _ "Molecular Weight= " & MW & " Daltons" ElseIf MW = 18 Then Cells(i, "B").Value = "No sequence selected" End If Next i End Sub 3. Sub Protweight() ' ' Protweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row 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.09 Case "C": MW = MW + 103.15 Case "D": MW = MW + 115.1 Case "E": MW = MW + 129.13 Case "F": MW = MW + 147.19 Case "G": MW = MW + 57.07 Case "H": MW = MW + 137.16 Case "I": MW = MW + 113.17 Case "K": MW = MW + 128.19 Case "L": MW = MW + 113.17 Case "M": MW = MW + 131.31 Case "N": MW = MW + 114.12 Case "P": MW = MW + 97.13 Case "Q": MW = MW + 128.15 Case "R": MW = MW + 156.2 Case "S": MW = MW + 87.09 Case "T": MW = MW + 101.12 Case "V": MW = MW + 99.15 Case "W": MW = MW + 186.23 Case "Y": MW = MW + 163.19 Y = Y + 1 Case Else: Z = Z + 1 End Select Next j MW = MW + 18 If MW 18 Then Cells(i, "B").Value = "Selection includes " & X - Z & _ " amino acids, Molecular Weight= " & _ Format(MW, "fixed") & " Daltons" Else: MsgBox Cells(i, "B").Value = "No Sequence Selected" End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "paulinoluciano" wrote in message ups.com... "I would like to use the VBA codes bellow that were writen by Gerry Shaw in order to calculate some properties of proteins and DNA. However how is this script such results can be done only for the selected cell (example A1) and the results are displayed as a message box. I would like to perform such operations for all cells in range (A:A) that contains data and that the results could be displayed on the left (B:B) of each one of these cells. Someone can help me with this procedure? Have I to use loop? Code 1: Sub IEP() ' ' IEP Macro ' Macro created 07/01/98 by Gerry Shaw ' X = Len(Selection.Text) If X = 0 Then MsgBox ("No selection made") For YY= 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "C" C = C + 1 Case "D" D = D + 1 Case "E" E = E + 1 Case "H" H = H + 1 Case "K" K = K + 1 Case "R" R = R + 1 Case "Y" Y = Y + 1 End Select Next YY ' Define array of nine elements containing the pKa values of amino acids, N and C-termini Dim pK(8) As Double pK(0) = 0.000446 ' C-term pK(1) = 0.0000851 ' Glu pK(2) = 0.000126 ' Asp pK(3) = 0.000000000661 ' Lys pK(4) = 0.00000000102 ' Arg pK(5) = 0.00000000447 ' Cys pK(6) = 0.000000912 ' His pK(7) = 0.000000000776 ' Tyr pK(8) = 0.000000000166 ' N-term For pH = 2 To 12 Step 0.1 ' calculates HH, the proton concentration HH = Exp(-pH * Log(10)) ' positive charge is function of proton conc, number of K, R and H and N-terminus pcharge = HH * K / (HH + pK(3)) + HH * R / (HH + pK(4)) + HH * H / (HH + pK(6)) + HH / (HH + pK(8)) ' negative charge is function of proton conc, number of Tyr, Cys, Glu, Asp and C-terminus ncharge = Y * (1 - (HH / (HH + pK(7)))) + C * (1 - (HH / (HH + pK(5)))) + 1 - (HH / (HH + pK(1))) + E * (1 - (HH / (HH + pK(1)))) + D * (1 - (HH / (HH + pK(2)))) ' exits for loop when pcharge is less than ncharge If (pcharge <= ncharge) Then Exit For Next pH MsgBox ("pKa = " & Format(pH, "fixed")) End Sub Code 2: The second macro, Nucweight, determines the molecular weight of a mouse selected DNA sequence. Sequence for this version must be uppercase text. To make it read lowercase, which is what you get with most sequence databases nowadays, change the Case commands; for example change Case "A" to Case "a" and make corresponding changes to the other Case commands. Sub Nucweight() ' ' Nucweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' This version for DNA sequences: for RNA MW for A = 329.2, U = 306.1, G = 345.2, C= 305.2 ' X = Len(Selection.Text) For YY = 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "A" MW = MW + 313.2 Case "T" MW = MW + 304.2 Case "G" MW = MW + 329.2 Case "C" MW = MW + 289.2 Case Else Z = Z + 1 End Select Next YY MW = MW + 18 If (MW 18) Then MsgBox ("Selection includes " & X - Z & " bases, " & "Molecular Weight= " & MW & " Daltons") If (MW = 18) Then MsgBox ("No sequence selected") End Sub Code 3: This macro determines counts the number of amino acids and determines the molecular weight of a mouse selected protein sequence Sub Protweight() ' ' Protweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' X = Len(Selection.Text) For YY = 1 To X Select Case Mid$(Selection.Text, YY, 1) Case "A" MW = MW + 71.09 Case "C" MW = MW + 103.15 Case "D" MW = MW + 115.1 Case "E" MW = MW + 129.13 Case "F" MW = MW + 147.19 Case "G" MW = MW + 57.07 Case "H" MW = MW + 137.16 Case "I" MW = MW + 113.17 Case "K" MW = MW + 128.19 Case "L" MW = MW + 113.17 Case "M" MW = MW + 131.31 Case "N" MW = MW + 114.12 Case "P" MW = MW + 97.13 Case "Q" MW = MW + 128.15 Case "R" MW = MW + 156.2 Case "S" MW = MW + 87.09 Case "T" MW = MW + 101.12 Case "V" MW = MW + 99.15 Case "W" MW = MW + 186.23 Case "Y" MW = MW + 163.19 Y = Y + 1 Case Else Z = Z + 1 End Select Next YY MW = MW + 18 If MW 18 Then MsgBox ("Selection includes " & X - Z & " amino acids, Molecular Weight= " & Format(MW, "fixed") & " Daltons") Else MsgBox ("No Sequence Selected") End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
Thank you Bob Phillips. The molecular weight VBA codes worked exactly
how I want however the IEP code have some strange problem! If you test the same sequence in different cells you will obtain distinct values. I can not understand what is happening... Luciano |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
Can you tell me which is which, and supply some test data that demonstrates
it? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "paulinoluciano" wrote in message ups.com... Thank you Bob Phillips. The molecular weight VBA codes worked exactly how I want however the IEP code have some strange problem! If you test the same sequence in different cells you will obtain distinct values. I can not understand what is happening... Luciano |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
I see what you mean now. Try this revision
BTW, the ProtWeight macro aggregates a variable Y, if the character is Y, but does not use it anywhere. Sub IEP() ' ' IEP Macro ' Macro created 07/01/98 by Gerry Shaw ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To iLastRow C = 0: D = 0: E = 0: H = 0: K = 0: R = 0: Y = 0 For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "C": C = C + 1 Case "D": D = D + 1 Case "E": E = E + 1 Case "H": H = H + 1 Case "K": K = K + 1 Case "R": R = R + 1 Case "Y": Y = Y + 1 End Select Next j ' Define array of nine elements containing the pKa ' values of amino acids , N And C - termini Dim pK(8) As Double pK(0) = 0.000446 ' C-term pK(1) = 0.0000851 ' Glu pK(2) = 0.000126 ' Asp pK(3) = 0.000000000661 ' Lys pK(4) = 0.00000000102 ' Arg pK(5) = 0.00000000447 ' Cys pK(6) = 0.000000912 ' His pK(7) = 0.000000000776 ' Tyr pK(8) = 0.000000000166 ' N-term For pH = 2 To 12 Step 0.1 ' calculates HH, the proton concentration HH = Exp(-pH * Log(10)) ' positive charge is function of proton conc, ' number of K, R and H and N -terminus pcharge = HH * K / (HH + pK(3)) + HH * R / _ (HH + pK(4)) + HH * H / (HH + pK(6)) + _ HH / (HH + pK(8)) ' negative charge is function of proton conc, ' number of Tyr, Cys, Glu,Asp and C-terminus ncharge = Y * (1 - (HH / (HH + pK(7)))) + _ C * (1 - (HH / (HH + pK(5)))) + 1 - _ (HH / (HH + pK(1))) + E * (1 - (HH / (HH + pK(1)))) + _ D * (1 - (HH / (HH + pK(2)))) ' exits for loop when pcharge is less than ncharge If (pcharge <= ncharge) Then Exit For Next pH Cells(i, "B").Value = "pKa = " & Format(pH, "fixed") Next i End Sub Sub Nucweight() ' ' Nucweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' This version for DNA sequences: for RNA MW for A = 329.2, ' U = 306.1, G = 345.2, C= 305.2 ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To iLastRow MW = 0: Z = 0 For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "A": MW = MW + 313.2 Case "T": MW = MW + 304.2 Case "G": MW = MW + 329.2 Case "C": MW = MW + 289.2 Case Else: Z = Z + 1 End Select Next j MW = MW + 18 If MW 18 Then Cells(i, "B").Value = "Selection includes " & Len(Cells(i, "A").Value) - Z & _ " bases, " & _ "Molecular Weight= " & MW & " Daltons" ElseIf MW = 18 Then Cells(i, "B").Value = "No sequence selected" End If Next i End Sub Sub Protweight() ' ' Protweight Macro ' Macro recorded 07/02/98 by Gerry Shaw ' iLastRow = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To iLastRow MW = 0: Y = 0: Z = 0 For j = 1 To Len(Cells(i, "A").Value) Select Case Mid$(Cells(i, "A").Value, j, 1) Case "A": MW = MW + 71.09 Case "C": MW = MW + 103.15 Case "D": MW = MW + 115.1 Case "E": MW = MW + 129.13 Case "F": MW = MW + 147.19 Case "G": MW = MW + 57.07 Case "H": MW = MW + 137.16 Case "I": MW = MW + 113.17 Case "K": MW = MW + 128.19 Case "L": MW = MW + 113.17 Case "M": MW = MW + 131.31 Case "N": MW = MW + 114.12 Case "P": MW = MW + 97.13 Case "Q": MW = MW + 128.15 Case "R": MW = MW + 156.2 Case "S": MW = MW + 87.09 Case "T": MW = MW + 101.12 Case "V": MW = MW + 99.15 Case "W": MW = MW + 186.23 Case "Y": MW = MW + 163.19 Y = Y + 1 Case Else: Z = Z + 1 End Select Next j MW = MW + 18 If MW 18 Then Cells(i, "B").Value = "Selection includes " & Len(Cells(i, "A").Value) - Z & _ " amino acids, Molecular Weight= " & _ Format(MW, "fixed") & " Daltons" Else Cells(i, "B").Value = "No Sequence Selected" End If Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "paulinoluciano" wrote in message ups.com... Thank you Bob Phillips. The molecular weight VBA codes worked exactly how I want however the IEP code have some strange problem! If you test the same sequence in different cells you will obtain distinct values. I can not understand what is happening... Luciano |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Loop for VBA code?
Thank you very much Bob Phillips!!
Now it is really working exactly how I would like to. This will be very helpful for my work. Do you have some experience manipulating texts like my another question for the forum related to texts manipulations and subsequences using specific rules? Best regards, Luciano |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
VBA Newbie: Help with Do Loop code | Excel Discussion (Misc queries) |