vba - vba用函数替换字符串
问题描述
我有一个 Excel 表,其中有一列(列标签为“标签”),其中包含代表某些项目的字符串。我用 UDF 编写了一个程序来转换列单元格中的字符串以适应后续任务。
Sub change_label()
Dim i as long, Label as long, LR As long, Cel As Range, rng As Range, WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Scope")
Label = WorksheetFunction.Match("Label", WS.Rows(1), 0)
LR = WS.Cells(WS.Rows.Count, Label).End(xlUp).Row
Set rng = WS.Range(Cells(2, Label), Cells(LR, Label))
For Each Cel In rng
Cel.Value = ChLabel(Cel.Value)
Next Cel
End Sub
Function ChLabel(CellRef As String) As String
If CellRef Like "Printer*" Then ChLabel = "PRINTER"
If CellRef Like "Ink*" Then ChLabel = "INK"
If CellRef Like "Ribbon*" Then ChLabel = "RIBBON"
If CellRef Like "A4Paper*" Then ChLabel = "A4"
End Function
但是,该函数偶尔会为匹配其中条件的字符串返回空白单元格(例如,包含字符串“Printer-AX1283”的单元格应转换为“PRINTER”,但返回的是空白单元格)。有时,当我重新打开工作表并重新运行程序时,它又会正常运行。
我应该改进我的功能以使其正常工作吗?
解决方案
一种可能的解决方案是使用不区分大小写的InStr
函数重写您的 UDF。
For txt in Array("Printer", "Ink", "Ribbon", "A4Paper")
If InStr(CellRef, txt, vbTextCompare) = 1 Then
ChLabel = UCase(txt)
Exit For
End If
Next txt