Loop for VBA code?

P

paulinoluciano

"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
 
B

Bob Phillips

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)
 
P

paulinoluciano

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
P

paulinoluciano

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subsequence value 1
Subsequence of characters 5
Scrabble Value calculation for Welsh words 0
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
Filter isn't working with a VBA Code 8
VLOOKUP for multiple colums 10
external links 2
how to debug microsoft errors coming from javascript? 2

Top