Use of LOOKUP in VBA

[M H]

Hi there,
I have created a function like this:

Option Explicit
Option Base 1

Public Function DeltaG(sInput As String)

Dim siRNA, NN() As String
Dim iNumofNN As Integer
Dim rNN As Range
Dim dG, dGNN As Single

With Worksheets("EnergyTable")
.Activate
rNN = .Range("B5:H20")
dG = .Range("C22").Value + .Range("C24").Value
End With

siRNA = sInput
For iNumofNN = 1 To Len(siRNA) - 1
NN(iNumofNN) = Mid(siRNA, iNumofNN, 2)
dGNN = Application.WorksheetFunction.VLookup(NN(iNumofNN), rNN, 2,
False)
dG = dG + dGNN
Next

DeltaG = dG
End Function

What does it do is to chop a string into an array of 2-letter strings,
match each short string to a table with all possible combinations of
2-letter strings for getting a value from the next column. The readout
of the function would be the sum of the values corresponding to the
array elements.

The function simply does not work as I expected, but I could not figure
out where the problem hides. Please help. Thanks.

Here is the link to my sample workbook:
http://boz094.ust.hk/EnergyTable.xls

 

[Jim Cone]

MH,

I made some revisions/corrections...
The rNN range had not been "Set".
The NN() array had not been dimensioned.
Some data types were changed.
It is past time for bed here and I will leave the
revised code for your perusal.
'------------------
Public Function DeltaG(sInput As String) As Double
Dim siRNA As String
Dim NN() As String
Dim iNumofNN As Long
Dim rNN As Excel.Range
Dim dG As Double
Dim dGNN As Double

With Worksheets("EnergyTable")
.Activate
Set rNN = .Range("B5:H20")
dG = .Range("C22").Value + .Range("C24").Value
End With

siRNA = sInput
ReDim NN(1 To Len(siRNA) - 1)

For iNumofNN = 1 To Len(siRNA) - 1
NN(iNumofNN) = Mid$(siRNA, iNumofNN, 2)
dGNN = Application.WorksheetFunction.VLookup(NN(iNumofNN), rNN, 2, False)
dG = dG + dGNN
Next
DeltaG = dG
End Function
'---------------------
Jim Cone
San Francisco, USA



Hi there,
I have created a function like this:
Option Explicit
Option Base 1
Public Function DeltaG(sInput As String)
Dim siRNA, NN() As String
Dim iNumofNN As Integer
Dim rNN As Range
Dim dG, dGNN As Single
With Worksheets("EnergyTable")
.Activate
rNN = .Range("B5:H20")
dG = .Range("C22").Value + .Range("C24").Value
End With
siRNA = sInput
For iNumofNN = 1 To Len(siRNA) - 1
NN(iNumofNN) = Mid(siRNA, iNumofNN, 2)
dGNN = Application.WorksheetFunction.VLookup(NN(iNumofNN), rNN, 2,
False)
dG = dG + dGNN
Next
DeltaG = dG
End Function

What does it do is to chop a string into an array of 2-letter strings,
match each short string to a table with all possible combinations of
2-letter strings for getting a value from the next column. The readout
of the function would be the sum of the values corresponding to the
array elements.
The function simply does not work as I expected, but I could not figure
out where the problem hides. Please help. Thanks.
Here is the link to my sample workbook: