根据代码表，将一行中的多个代码转成对应汉字

 

最近写了个代码转汉字的VBA，使用VLookup时会报1004错误，经过dsd999网友的帮助，发现原始是数据类型的问题，如果代码里全是数字，需要使用CInt函数将传入的参数进行类型转换！具体转换的代码如下：

 

 

Function toNamesOneByOne(codes As String, startRow As Long, endRow As Long) 'MsgBox codes 'MsgBox startRow 'MsgBox endRow Dim arrCode() As String Dim l As Long Dim i As Long Dim j As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode For j = startRow To endRow 'MsgBox Sheets("Sheet2").Range("B" & j).Value If tmpCode = Sheets("Sheet2").Range("B" & j).Value Then tmpName = Sheets("Sheet2").Range("A" & j).Value Exit For End If Next If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNamesOneByOne = namesEnd Function'将逗号分隔的代码字符串，转换成逗号分隔的汉字字符串'VBA 运行时错误1004 不能取得类WorksheetFunction的VLookup属性Function toNames(codes As String) 'MsgBox codes Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l 'MsgBox arrCode(i) 'MsgBox Application.WorksheetFunction.VLookup(arrCode(i), Sheets("Sheet2").range("A2:B7"), 1, False) 'names = names + Application.WorksheetFunction.VLookup(arrCode(i), Sheets("Sheet2").range("A2:B7"), 1, False) tmpCode = arrCode(i) tmpName = tmpCode tmpName = Application.WorksheetFunction.Lookup(arrCode(i), Sheets("Sheet2").Range("B2:B7"), Sheets("Sheet2").Range("A2:A7")) If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNames = namesEnd FunctionFunction toNamesArg(codes As String, colCode As Range, colName As Range) 'MsgBox codes Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode 'MsgBox tmpCode tmpName = Application.WorksheetFunction.Lookup(tmpCode, colCode, colName) If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next 'MsgBox names toNamesArg = namesEnd FunctionFunction toNamesRange(codes As String, rangeCodeName As Range) 'On Error Resume Next Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String 'MsgBox codes arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String Dim tmpName As String For i = 0 To l tmpCode = arrCode(i) tmpName = tmpCode 'MsgBox tmpCode tmpName = Application.WorksheetFunction.VLookup(tmpCode, rangeCodeName, 2, 0) 'MsgBox tmpName If names = "" Then names = names + tmpName Else names = names + "," + tmpName End If Next toNamesRange = namesEnd FunctionSub toNamesMacro() 'On Error Resume Next Dim codes As String Dim arrCode() As String Dim l As Long Dim i As Long Dim names As String Dim rangeDict As Range codes = Range("A2").Value 'MsgBox codes arrCode = Split(codes, ",") l = UBound(arrCode) names = "" Dim tmpCode As String 'Dim colCode As Range 'Dim colName As Range 'Dim rangeCodeName As Range Set colCode = Worksheets("Sheet2").Range("B2:B7") Set colName = Worksheets("Sheet2").Range("A2:A7") Set rangeCodeName = Worksheets("Sheet2").Range("$B$2:$C$7") For i = 0 To l tmpCode = arrCode(i) 'MsgBox tmpCode 'MsgBox Application.WorksheetFunction.Lookup(tmpCode, colCode, colName) tmpCode = Application.WorksheetFunction.VLookup(tmpCode, rangeCodeName, 2, 0) 'MsgBox tmpCode Next Range("K2").Value = namesEnd SubSub test() Set colCode = Worksheets("Sheet2").Range("B2:B7") answer = Application.WorksheetFunction.Min(colCode) 'MsgBox answer Set colName = Worksheets("Sheet2").Range("A2:A7") answer = Application.WorksheetFunction.Max(colName) 'MsgBox answer 'MsgBox Sheets("Sheet2").Range("A1").Value MsgBox ActiveWorkbook.names("gjBegin").RefersToRange.ValueEnd Sub