时间戳

首页 > 解决方案 > Excel VBA Vlookup 单个字符

excel - Excel VBA Vlookup 单个字符

问题描述

我有一个字符串，我需要将其转换为十六进制等价物。对于文本字符，十六进制值可能会因情况而异，因此在第二张工作表中，我制作了一个表格，我可以根据需要使用我的 AZ...az..1-0 字符及其相关的十六进制值进行修改。

我已经确认我已经正确设置了 VLookup 的范围，并且我已经确认我一次将一个字符传递给它，但它坚持认为它在 Vlookup 上找不到任何值。我在这里想念什么？

Sub LoopThroughString()
Dim wbBook As Workbook
Dim tblSheet As Worksheet
Dim wrkSheet As Worksheet
Dim Counter As Integer
Dim myString, myChar As String
Dim vRng, chrRng As Range
Dim sRes As Variant

Set wbBook = ThisWorkbook
With wbBook
    Set wrkSheet = .Worksheets("wrkSheet")
    Set tblSheet = .Worksheets("tblSheet")
End With

With tblSheet
    Set vRng = .Range("A1", .Range("B1").End(xlDown))
End With

myString = Range("A1").Value 'define string


For Counter = 1 To Len(myString)
    On Error Resume Next
    Err.Clear
    myChar = Mid(myString, Counter, 1)
    sRes = Application.VLookup(myChar, vRng, 1, False)
    If IsError(sRes) = False Then
        Cells(2, 1 + Counter).Value = chrRng
    Else
        Debug.Print "Count not find value: " & myChar
    End If

Next

End Sub

编辑：这是表格的屏幕截图，其中包含要查找的字符 在此处输入图像描述

标签： excelvbavlookup

解决方案

答案是我最初将我的表格安排为十六进制值 | Char 正如@ScottHoltzman 指出的那样，我需要它来安排 Char | 十六进制值。它似乎不起作用的原因是因为我有一个未使用的变量被写入单元格而不是 sRes 变量。下面的工作代码，但它不区分大小写

Sub LoopThroughString()
Dim wbBook As Workbook
Dim tblSheet As Worksheet
Dim wrkSheet As Worksheet
Dim Counter As Integer
Dim myString as String, myChar As String
Dim vRng As Range
Dim sRes As Variant

Set wbBook = ThisWorkbook
With wbBook
    Set wrkSheet = .Worksheets("wrkSheet")
    Set tblSheet = .Worksheets("tblSheet")
End With

With tblSheet
    Set vRng = .Range("A1", .Range("B1").End(xlDown))
End With

With wrkSheet
    myString = .Range("A1").Value 'define string
End With


For Counter = 1 To Len(myString)
    On Error Resume Next
    Err.Clear
    myChar = Mid(myString, Counter, 1)
    sRes = Application.VLookup(myChar, vRng, 2, False)
    If IsError(sRes) = False Then
        Debug.Print ""
        Cells(2, 1 + Counter).Value = sRes
    Else
        Debug.Print "Couldn't find value: " & myChar
    End If

Next

End Sub

推荐阅读