Loop for VBA code

paulinoluciano

New Member
Joined
Dec 12, 2005
Messages
11
"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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Watch MrExcel Video

Forum statistics

Threads
1,118,068
Messages
5,570,003
Members
412,304
Latest member
citrus
Top