vba to convert text string

[dnickelson]

I'm looking to convert a text string in a cell, letter by letter.
I can do it with vlookup by grabbing each letter into a cell, then running vlookup with the conversion table, but the text can be up to 50 characters long and I'm looking to do this with 40 or so rows with 4 - 5 column.
I can't fit 50 vlookup formulas into 1 formula because of length, and using a cell per letter, then combining the results in a single cell is kind of a logistics nightmare.

I think a vba loop would work pretty good, but am unsure how to write it, let alone link it to a lookup table. Any examples available?

the text would be similar to a password and the conversion would be to hex, thus the text "nickelson" would be returned as "4E49434B454C534F4E"

[DonkeyOte]

can you tell us where the lookup is and what comprises it?

eg

Lookup on Sheet2 Range A:B
A contains letter
B contains conversion

[dnickelson]

Yes, actually I'm getting pretty close, I'll post my code below
basically I have sheet1 with a range of cells with usernames, passwords, links, etc, from, say, A1 to D5
I can put the conversion table wherever, probably different sheet, but for now on sheet1 from F10:G90
I want Sheet2 A1:D5 to contain the same cells as Sheet1, but each character in each cell converted according to the conversion table

I think I have the conversion in this formula

Sub convert()
Dim target As Range
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
i = 1
Set target = Range("E5")
text = target.Value
For i = 1 To 30
Set lookRange = Worksheets("Sheet1").Range("B1:C80")
letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = ""
Else
Range("D1").Value = Range("D1").Value & res
End If
Next
End Sub

maybe a little extra here too, but I haven't come up with the loop for looking each cell one at a time and converting. Having trouble getting my head around skipping form cell to cell. Any help would be appreciated
(also, the above formula doesn't follow the parameters I set for where the data is\goes yet, but it does convert a text string

[Seti]

How about making this a function instead. Then just apply the function cell by cell. This is untested but I converted your macro to a function.

Function convert(target As String) As Variant
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
i = 1
text = target.Value
For i = 1 To 30
Set lookRange = Worksheets("Sheet1").Range("B1:C80")
letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = ""
Else
convert = convert & res
End If
Next
End Function

[DonkeyOte]

Just put a double loop in there by column and row so range A1:D5 gets processed - presumed Sheet1 held original data and Sheet2 was where converted data was to go - is that what you wanted? Again not tested...so may have missed some blatant point!

Sub convert()
Dim target As Range
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant

Sheets("sheet1").Select

c = 1
Do Until c > 4
    r = 1
    Do Until r > 5
        i = 1
        Set target = Cells(r, c)
        text = target.Value
        For i = 1 To 30
            Set lookRange = Worksheets("Sheet1").Range("B1:C80")
            letter = Mid(text, i, 1)
            res = Application.VLookup(letter, lookRange, 2, False)
            If IsError(res) Then
                res = ""
            Else
                Sheets("sheet2").Cells(r,c) = Sheets("Sheet1").Cells(r,c) & res
            End If
        Next i
    r = r + 1
    Loop
c = c + 1
Loop

End Sub

[dnickelson]

works pretty slick as a function,
here's a version that converts the sheet at once, finall figured it out, but it wouldn't do numbers. May work now, the funciton had the same issue and that's resolved. (conversion table was saved as text, but had to select the numbers, and click ignore error on value stored as text)

Sub convert()

Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
Dim a As Long
Dim b As Long

Set lookRange = Worksheets("Sheet2").Range("A1:B80")

For a = 1 To 6
For b = 1 To 50
text = Sheets("pas").Cells(b, a).Value
If text = "" Then
Sheets("Sheet1").Cells(b, a).Value = ""
Else
Sheets("Sheet1").Cells(b, a).Value = "a"
End If

For i = 1 To 30

letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = letter
Else
Sheets("Sheet1").Cells(b, a).Value = Sheets("Sheet1").Cells(b, a).Value & res
End If
Next
Next
Next

End Sub

[NateO]

I'm looking to convert a text string in a cell, letter by letter.
....
the text would be similar to a password and the conversion would be to hex, thus the text "nickelson" would be returned as "4E49434B454C534F4E"
{snip}

The following kind of sort of seemed to work:

Sub hexTime()
Dim b() As Byte, myStr As String, myArr As Variant, i As Long
For Each cl In [a1:a50]
****With WorksheetFunction
********myArr = .Transpose(.Transpose(cl.Resize(, 5).Value))
****End With
****myStr = myStr & Join(myArr, vbNullString)
Next
b = StrConv(myStr, vbFromUnicode): Let myStr = Empty
For i = LBound(b) To UBound(b)
****myStr = myStr & hex(b(i))
Next
Debug.Print myStr
End Sub

But, nickelson returned:

6E69636B656C736F6E versus
4E49434B454C534F4E

You can see what you expected to be 4's came back as 6's and a 5 in one instance became a 7.

[tusharm]

You might also want to visit the Excel | VBA page of my web site and follow the link to 'Simple Encoding'
http://www.tushar-mehta.com/excel/vb...e_encoding.htm

Yes, actually I'm getting pretty close, I'll post my code below
basically I have sheet1 with a range of cells with usernames, passwords, links, etc, from, say, A1 to D5
{snip}

[dnickelson]

thanks all, got it working, as far as the 4-6, 5-7 difference, I would assume your code returned each letter as CAPS maybe, since that would be the difference between upper and lower case.

otherwise, both the function and the loop examples worked. I ended up going with the function because it's a little more portable, but thanks to all for helping make VBA make a little more sense.

PS - i didn't get to try out Nate's version yet, but it looks like that might be the shortest path

Thanks again, and if anyone knows any examples of sqlrequest run in vba, I'll be posting a question for you soon. Returning the results has got me lost.

[tusharm]

That's because of VLOOKUP -- a case-insensitive function.

{snip}

But, nickelson returned:

6E69636B656C736F6E versus
4E49434B454C534F4E

You can see what you expected to be 4's came back as 6's and a 5 in one instance became a 7.