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
This is a discussion on vba to convert text string within the Excel Questions forums, part of the Question Forums category; I'm looking to convert a text string in a cell, letter by letter. I can do it with vlookup by ...
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"
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
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
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
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!
Code: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
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
The following kind of sort of seemed to work:Originally Posted by dnickelson
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.
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
Originally Posted by dnickelson
Tushar Mehta (Microsoft MVP Excel 2000-present)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
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.
That's because of VLOOKUP -- a case-insensitive function.
Originally Posted by NateO
Tushar Mehta (Microsoft MVP Excel 2000-present)
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office
Like this thread? Share it with others