excel - 从给定范围中删除标点符号的 VBA 循环

Question

我对 VBA 很陌生，我需要创建一个循环，从字符串中删除某些标点符号，这些标点符号在我的电子表格中被引用为值 A2:A33。这就是我到目前为止所拥有的，在替换功能上苦苦挣扎。任何帮助将不胜感激谢谢!

Function CleanDescription(rawDescription As String) As String
Dim punctuationRng As Range
Set punctuationRng = Worksheets("ToRemove").Range("A2:A33")

Dim cleanDesc As String
cleanDesc = rawDescription

For i = 1 To punctuationRng.Count
cleanDesc = Replace(cleanDesc, punctuationRng, "")
Next i

CleanDescription = cleanDesc
End Function

Answer 1

您可以用 For Each 替换字符串中存在的一系列单元格中的每个值。 :

Function CleanDescription(rawDescription As String) As String
Dim punctuationRng As Range
Set punctuationRng = Worksheets("ToRemove").Range("A2:A33")

Dim cleanDesc As String
cleanDesc = rawDescription

For Each punctuationCell In punctuationRng
    cleanDesc = Replace(cleanDesc, punctuationCell.Value, "")
Next punctuationCell

CleanDescription = cleanDesc
End Function

如果您不喜欢 For Each，您也可以使用 For Loop 来实现它。 :

Dim cellValue As String
For r = 1 to punctuationRng.Rows.Count
    For c = 1 to punctuationRng.Columns.Count
        cellValue = punctuationRng.cells(r, c).Value
        cleanDesc = Replace(cleanDesc, cellValue, "")
    Next c
Next r