software

quot;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 (quot;No selection madequot;)

For YY= 1 To X
Select Case Mid$(Selection.Text, YY, 1)
Case quot;Cquot;
C = C 1
Case quot;Dquot;
D = D 1
Case quot;Equot;
E = E 1
Case quot;Hquot;
H = H 1
Case quot;Kquot;
K = K 1
Case quot;Rquot;
R = R 1
Case quot;Yquot;
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 lt;= ncharge) Then Exit For
Next pH
MsgBox (quot;pKa = quot; amp; Format(pH, quot;fixedquot;))
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 quot;Aquot; to Case quot;aquot; 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 quot;Aquot;
MW = MW 313.2
Case quot;Tquot;
MW = MW 304.2
Case quot;Gquot;
MW = MW 329.2
Case quot;Cquot;
MW = MW 289.2
Case Else
Z = Z 1
End Select
Next YY
MW = MW 18
If (MW gt; 18) Then MsgBox (quot;Selection includes quot; amp; X - Z amp; quot; bases, quot; amp;
quot;Molecular Weight= quot; amp; MW amp; quot; Daltonsquot;)
If (MW = 18) Then MsgBox (quot;No sequence selectedquot;)
End SubCode 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 quot;Aquot;
MW = MW 71.09
Case quot;Cquot;
MW = MW 103.15
Case quot;Dquot;
MW = MW 115.1
Case quot;Equot;
MW = MW 129.13
Case quot;Fquot;
MW = MW 147.19
Case quot;Gquot;
MW = MW 57.07
Case quot;Hquot;
MW = MW 137.16
Case quot;Iquot;
MW = MW 113.17
Case quot;Kquot;
MW = MW 128.19
Case quot;Lquot;
MW = MW 113.17
Case quot;Mquot;
MW = MW 131.31
Case quot;Nquot;
MW = MW 114.12
Case quot;Pquot;
MW = MW 97.13
Case quot;Qquot;
MW = MW 128.15
Case quot;Rquot;
MW = MW 156.2
Case quot;Squot;
MW = MW 87.09
Case quot;Tquot;
MW = MW 101.12
Case quot;Vquot;
MW = MW 99.15
Case quot;Wquot;
MW = MW 186.23
Case quot;Yquot;
MW = MW 163.19
Y = Y 1
Case Else
Z = Z 1
End Select
Next YY
MW = MW 18
If MW gt; 18 Then MsgBox (quot;Selection includes quot; amp; X - Z amp; quot; amino acids,
Molecular Weight= quot; amp; Format(MW, quot;fixedquot;) amp; quot; Daltonsquot;) Else MsgBox
(quot;No Sequence Selectedquot;)
End Sub1.

Sub IEP()
'
' IEP Macro
' Macro created 07/01/98 by Gerry Shaw
'
iLastRow = Cells(Rows.Count, quot;aquot;).End(xlUp).Row
For i = 1 To iLastRow
For j = 1 To Len(Cells(i, quot;Aquot;).Value)
Select Case Mid$(Cells(i, quot;Aquot;).Value, j, 1)
Case quot;Cquot;: C = C 1
Case quot;Dquot;: D = D 1
Case quot;Equot;: E = E 1
Case quot;Hquot;: H = H 1
Case quot;Kquot;: K = K 1
Case quot;Rquot;: R = R 1
Case quot;Yquot;: 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 lt;= ncharge) Then Exit For
Next pH
Cells(i, quot;Bquot;).Value = quot;pKa = quot; amp; Format(pH, quot;fixedquot;)
Next i

End Sub2.

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, quot;aquot;).End(xlUp).Row
For i = 1 To iLastRow
For j = 1 To Len(Cells(i, quot;Aquot;).Value)
Select Case Mid$(Cells(i,quot;Aquot;).value,j, 1)
Case quot;Aquot;: MW = MW 313.2
Case quot;Tquot;: MW = MW 304.2
Case quot;Gquot;: MW = MW 329.2
Case quot;Cquot;: MW = MW 289.2
Case Else: Z = Z 1
End Select
Next j
MW = MW 18
If MW gt; 18 Then
Cells(i, quot;Bquot;).Value = quot;Selection includes quot; amp; X - Z amp; quot; bases, quot;
amp; _
quot;Molecular Weight= quot; amp; MW amp; quot; Daltonsquot;
ElseIf MW = 18 Then
Cells(i, quot;Bquot;).Value = quot;No sequence selectedquot;
End If
Next i
End Sub

3.

Sub Protweight()
'
' Protweight Macro
' Macro recorded 07/02/98 by Gerry Shaw
'

iLastRow = Cells(Rows.Count, quot;aquot;).End(xlUp).Row
For i = 1 To iLastRow
For j = 1 To Len(Cells(i, quot;Aquot;).Value)
Select Case Mid$(Cells(i, quot;Aquot;).Value, j, 1)
Case quot;Aquot;: MW = MW 71.09
Case quot;Cquot;: MW = MW 103.15
Case quot;Dquot;: MW = MW 115.1
Case quot;Equot;: MW = MW 129.13
Case quot;Fquot;: MW = MW 147.19
Case quot;Gquot;: MW = MW 57.07
Case quot;Hquot;: MW = MW 137.16
Case quot;Iquot;: MW = MW 113.17
Case quot;Kquot;: MW = MW 128.19
Case quot;Lquot;: MW = MW 113.17
Case quot;Mquot;: MW = MW 131.31
Case quot;Nquot;: MW = MW 114.12
Case quot;Pquot;: MW = MW 97.13
Case quot;Qquot;: MW = MW 128.15
Case quot;Rquot;: MW = MW 156.2
Case quot;Squot;: MW = MW 87.09
Case quot;Tquot;: MW = MW 101.12
Case quot;Vquot;: MW = MW 99.15
Case quot;Wquot;: MW = MW 186.23
Case quot;Yquot;: MW = MW 163.19
Y = Y 1
Case Else: Z = Z 1
End Select
Next j
MW = MW 18
If MW gt; 18 Then
Cells(i, quot;Bquot;).Value = quot;Selection includes quot; amp; X - Z amp; _
quot; amino acids, Molecular Weight= quot; amp; _
Format(MW, quot;fixedquot;) amp; quot; Daltonsquot;
Else: MsgBox
Cells(i, quot;Bquot;).Value = quot;No Sequence Selectedquot;
End If
Next i
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;paulinolucianoquot; gt; wrote in message ups.com...
gt; quot;I would like to use the VBA codes bellow that were writen by Gerry
gt; Shaw in order to calculate some properties of proteins and DNA. However
gt; how is this script such results can be done only for the selected cell
gt; (example A1) and the results are displayed as a message box.
gt; I would like to perform such operations for all cells in range (A:A)
gt; that contains data and that the results could be displayed on the left
gt; (B:B) of each one of these cells. Someone can help me with this
gt; procedure? Have I to use loop?
gt;
gt; Code 1:
gt;
gt; Sub IEP()
gt; '
gt; ' IEP Macro
gt; ' Macro created 07/01/98 by Gerry Shaw
gt; '
gt; X = Len(Selection.Text)
gt; If X = 0 Then MsgBox (quot;No selection madequot;)
gt;
gt; For YY= 1 To X
gt; Select Case Mid$(Selection.Text, YY, 1)
gt; Case quot;Cquot;
gt; C = C 1
gt; Case quot;Dquot;
gt; D = D 1
gt; Case quot;Equot;
gt; E = E 1
gt; Case quot;Hquot;
gt; H = H 1
gt; Case quot;Kquot;
gt; K = K 1
gt; Case quot;Rquot;
gt; R = R 1
gt; Case quot;Yquot;
gt; Y = Y 1
gt; End Select
gt; Next YY
gt;
gt; ' Define array of nine elements containing the pKa values of amino
gt; acids, N and C-termini
gt;
gt; Dim pK(8) As Double
gt;
gt; pK(0) = 0.000446 ' C-term
gt; pK(1) = 0.0000851 ' Glu
gt; pK(2) = 0.000126 ' Asp
gt; pK(3) = 0.000000000661 ' Lys
gt; pK(4) = 0.00000000102 ' Arg
gt; pK(5) = 0.00000000447 ' Cys
gt; pK(6) = 0.000000912 ' His
gt; pK(7) = 0.000000000776 ' Tyr
gt; pK(8) = 0.000000000166 ' N-term
gt;
gt; For pH = 2 To 12 Step 0.1
gt;
gt; ' calculates HH, the proton concentration
gt;
gt; HH = Exp(-pH * Log(10))
gt;
gt; ' positive charge is function of proton conc, number of K, R and H and
gt; N-terminus
gt;
gt; pcharge = HH * K / (HH pK(3)) HH * R / (HH pK(4)) HH * H / (HH
gt; pK(6)) HH / (HH pK(8))
gt;
gt; ' negative charge is function of proton conc, number of Tyr, Cys, Glu,
gt; Asp and C-terminus
gt;
gt; ncharge = Y * (1 - (HH / (HH pK(7)))) C * (1 - (HH / (HH pK(5))))
gt; 1 - (HH / (HH pK(1))) E * (1 - (HH / (HH pK(1)))) D * (1 -
gt; (HH / (HH pK(2))))
gt;
gt; ' exits for loop when pcharge is less than ncharge
gt;
gt; If (pcharge lt;= ncharge) Then Exit For
gt; Next pH
gt; MsgBox (quot;pKa = quot; amp; Format(pH, quot;fixedquot;))
gt; End Sub
gt;
gt; Code 2:
gt;
gt; The second macro, Nucweight, determines the molecular weight of a mouse
gt; selected DNA sequence. Sequence for this version must be uppercase
gt; text. To make it read lowercase, which is what you get with most
gt; sequence databases nowadays, change the Case commands; for example
gt; change Case quot;Aquot; to Case quot;aquot; and make corresponding changes to the other
gt; Case commands.
gt;
gt;
gt; Sub Nucweight()
gt; '
gt; ' Nucweight Macro
gt; ' Macro recorded 07/02/98 by Gerry Shaw
gt; ' This version for DNA sequences: for RNA MW for A = 329.2, U = 306.1,
gt; G = 345.2, C= 305.2
gt; '
gt; X = Len(Selection.Text)
gt; For YY = 1 To X
gt; Select Case Mid$(Selection.Text, YY, 1)
gt; Case quot;Aquot;
gt; MW = MW 313.2
gt; Case quot;Tquot;
gt; MW = MW 304.2
gt; Case quot;Gquot;
gt; MW = MW 329.2
gt; Case quot;Cquot;
gt; MW = MW 289.2
gt; Case Else
gt; Z = Z 1
gt; End Select
gt; Next YY
gt; MW = MW 18
gt; If (MW gt; 18) Then MsgBox (quot;Selection includes quot; amp; X - Z amp; quot; bases, quot; amp;
gt; quot;Molecular Weight= quot; amp; MW amp; quot; Daltonsquot;)
gt; If (MW = 18) Then MsgBox (quot;No sequence selectedquot;)
gt; End Sub
gt;
gt;
gt; Code 3:
gt;
gt; This macro determines counts the number of amino acids and determines
gt; the molecular weight of a mouse selected protein sequence
gt;
gt; Sub Protweight()
gt; '
gt; ' Protweight Macro
gt; ' Macro recorded 07/02/98 by Gerry Shaw
gt; '
gt; X = Len(Selection.Text)
gt; For YY = 1 To X
gt; Select Case Mid$(Selection.Text, YY, 1)
gt; Case quot;Aquot;
gt; MW = MW 71.09
gt; Case quot;Cquot;
gt; MW = MW 103.15
gt; Case quot;Dquot;
gt; MW = MW 115.1
gt; Case quot;Equot;
gt; MW = MW 129.13
gt; Case quot;Fquot;
gt; MW = MW 147.19
gt; Case quot;Gquot;
gt; MW = MW 57.07
gt; Case quot;Hquot;
gt; MW = MW 137.16
gt; Case quot;Iquot;
gt; MW = MW 113.17
gt; Case quot;Kquot;
gt; MW = MW 128.19
gt; Case quot;Lquot;
gt; MW = MW 113.17
gt; Case quot;Mquot;
gt; MW = MW 131.31
gt; Case quot;Nquot;
gt; MW = MW 114.12
gt; Case quot;Pquot;
gt; MW = MW 97.13
gt; Case quot;Qquot;
gt; MW = MW 128.15
gt; Case quot;Rquot;
gt; MW = MW 156.2
gt; Case quot;Squot;
gt; MW = MW 87.09
gt; Case quot;Tquot;
gt; MW = MW 101.12
gt; Case quot;Vquot;
gt; MW = MW 99.15
gt; Case quot;Wquot;
gt; MW = MW 186.23
gt; Case quot;Yquot;
gt; MW = MW 163.19
gt; Y = Y 1
gt; Case Else
gt; Z = Z 1
gt; End Select
gt; Next YY
gt; MW = MW 18
gt; If MW gt; 18 Then MsgBox (quot;Selection includes quot; amp; X - Z amp; quot; amino acids,
gt; Molecular Weight= quot; amp; Format(MW, quot;fixedquot;) amp; quot; Daltonsquot;) Else MsgBox
gt; (quot;No Sequence Selectedquot;)
gt; End Sub
gt;
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...
LucianoCan 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)

quot;paulinolucianoquot; gt; wrote in message ups.com...
gt; Thank you Bob Phillips. The molecular weight VBA codes worked exactly
gt; how I want however the IEP code have some strange problem! If you test
gt; the same sequence in different cells you will obtain distinct values. I
gt; can not understand what is happening...
gt; Luciano
gt;
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, quot;aquot;).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, quot;Aquot;).Value)
Select Case Mid$(Cells(i, quot;Aquot;).Value, j, 1)
Case quot;Cquot;: C = C 1
Case quot;Dquot;: D = D 1
Case quot;Equot;: E = E 1
Case quot;Hquot;: H = H 1
Case quot;Kquot;: K = K 1
Case quot;Rquot;: R = R 1
Case quot;Yquot;: 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 lt;= ncharge) Then Exit For
Next pH
Cells(i, quot;Bquot;).Value = quot;pKa = quot; amp; Format(pH, quot;fixedquot;)
Next i

End SubSub 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, quot;aquot;).End(xlUp).Row
For i = 1 To iLastRow
MW = 0: Z = 0
For j = 1 To Len(Cells(i, quot;Aquot;).Value)
Select Case Mid$(Cells(i, quot;Aquot;).Value, j, 1)
Case quot;Aquot;: MW = MW 313.2
Case quot;Tquot;: MW = MW 304.2
Case quot;Gquot;: MW = MW 329.2
Case quot;Cquot;: MW = MW 289.2
Case Else: Z = Z 1
End Select
Next j
MW = MW 18
If MW gt; 18 Then
Cells(i, quot;Bquot;).Value = quot;Selection includes quot; amp; Len(Cells(i,
quot;Aquot;).Value) - Z amp; _
quot; bases, quot; amp; _
quot;Molecular Weight= quot; amp; MW amp; quot; Daltonsquot;
ElseIf MW = 18 Then
Cells(i, quot;Bquot;).Value = quot;No sequence selectedquot;
End If
Next i
End SubSub Protweight()
'
' Protweight Macro
' Macro recorded 07/02/98 by Gerry Shaw
'

iLastRow = Cells(Rows.Count, quot;aquot;).End(xlUp).Row
For i = 1 To iLastRow
MW = 0: Y = 0: Z = 0
For j = 1 To Len(Cells(i, quot;Aquot;).Value)
Select Case Mid$(Cells(i, quot;Aquot;).Value, j, 1)
Case quot;Aquot;: MW = MW 71.09
Case quot;Cquot;: MW = MW 103.15
Case quot;Dquot;: MW = MW 115.1
Case quot;Equot;: MW = MW 129.13
Case quot;Fquot;: MW = MW 147.19
Case quot;Gquot;: MW = MW 57.07
Case quot;Hquot;: MW = MW 137.16
Case quot;Iquot;: MW = MW 113.17
Case quot;Kquot;: MW = MW 128.19
Case quot;Lquot;: MW = MW 113.17
Case quot;Mquot;: MW = MW 131.31
Case quot;Nquot;: MW = MW 114.12
Case quot;Pquot;: MW = MW 97.13
Case quot;Qquot;: MW = MW 128.15
Case quot;Rquot;: MW = MW 156.2
Case quot;Squot;: MW = MW 87.09
Case quot;Tquot;: MW = MW 101.12
Case quot;Vquot;: MW = MW 99.15
Case quot;Wquot;: MW = MW 186.23
Case quot;Yquot;: MW = MW 163.19
Y = Y 1
Case Else: Z = Z 1
End Select
Next j
MW = MW 18
If MW gt; 18 Then
Cells(i, quot;Bquot;).Value = quot;Selection includes quot; amp; Len(Cells(i,
quot;Aquot;).Value) - Z amp; _
quot; amino acids, Molecular Weight= quot; amp; _
Format(MW, quot;fixedquot;) amp; quot; Daltonsquot;
Else
Cells(i, quot;Bquot;).Value = quot;No Sequence Selectedquot;
End If
Next i
End Sub
--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;paulinolucianoquot; gt; wrote in message ups.com...
gt; Thank you Bob Phillips. The molecular weight VBA codes worked exactly
gt; how I want however the IEP code have some strange problem! If you test
gt; the same sequence in different cells you will obtain distinct values. I
gt; can not understand what is happening...
gt; Luciano
gt;
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