vlookup in VBA

[Nicawette]

Hi

I have in Cell A1 a string of chars like AABBCC
I have a correspondence table in sheet3

A -> B
B-> C
C -> D

I have made this function to return a modified string (from AABBCC to
BBCCDD) but it returns me #VALUE? someone can help me?

Public Function Encode(InputString As String) As String
Dim intLen As Integer
Dim intCount As Integer
Set myRng = Worksheets("sheet3").Range("G1:H36")

intLen = Len(InputString)
For intCount = 1 To intLen
Encode = Encode & _
Application.VLookup(Mid(InputString, intCount, 1), myRng,
2, False)
Next
End Function

thank you

 

[Jim Cone]

1. Declare the myRng variable...
Dim myRng as Range

2. Put quotation marks around the argument when calling the function...
=Encode("AABBCC")
-or-
Use a cell reference when calling the function...
=Encode(A1)
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Nicawette" <[email protected]>
wrote in message
Hi

I have in Cell A1 a string of chars like AABBCC
I have a correspondence table in sheet3

A -> B
B -> C
C -> D

I have made this function to return a modified string (from AABBCC to
BBCCDD) but it returns me #VALUE? someone can help me?

Public Function Encode(InputString As String) As String
Dim intLen As Integer
Dim intCount As Integer
Set myRng = Worksheets("sheet3").Range("G1:H36")

intLen = Len(InputString)
For intCount = 1 To intLen
Encode = Encode & _
Application.VLookup(Mid(InputString, intCount, 1), myRng,
2, False)
Next
End Function

thank you

 

[Nicawette]

Hi Jim

Thank you the problem was the declaration, so far so close

tx

 

[Jim Cone]

You are welcome.
Also FWIW, here is a slightly different approach...
'--
Public Function Encode(InputString As String) As String
Dim intLen As Long
Dim intCount As Long

intLen = Len(InputString)
For intCount = 1 To intLen
Encode = Encode & Chr$(VBA.Asc(Mid$(InputString, intCount, 1)) + 1)
Next
End Function
--
Jim Cone
Portland, Oregon USA
http://tinyurl.com/PrimitiveSoftware

..
..
..

"Nicawette" <[email protected]>
wrote in message
Hi Jim
Thank you the problem was the declaration, so far so close
tx