Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in VBA
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in VBA
Hi Jim
Thank you the problem was the declaration, so far so close tx |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup in VBA
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" wrote in message ... Hi Jim Thank you the problem was the declaration, so far so close tx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup problem - unable to get the vlookup property | Excel Programming | |||
using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |