vba - 如何删除修改字符串以从新工作表中删除字母

Question

所以我一直在尝试创建一个宏来格式化打开一个新的工作文件，然后修改 1 列以从数字字符串中删除字母（例如，20180717a 变为 20180717）。我已经尝试了几件事并想出了这段代码，但它似乎无法正常工作：

Sub Creating_progress_reports()

    Dim wb As Workbook
    Dim myfilename As String

    myfilename = "xxxx.xlsx"
    Set wb = Workbooks.Open(myfilename)

    Dim str1 As String
    Dim str2 As String
    Dim rngTemp As Range
    Dim rngCell As Range

    str1 = "a"
    str2 = "b"
    str3 = "c"
    str4 = "d"

    With Workbooks("xxxx.xlsx").Sheets("xxxx")
    Set rngTemp = Columns(6).CurrentRegion

    For Each rngCell In rngTemp

    If InStr(1, rngCell, str1) > 0 Then
        rngCell = Replace(rngCell.Value, str1, "")
    End If

    If InStr(1, rngCell, str2) > 0 Then
        rngCell = Replace(rngCell.Value, str2, "")
    End If

    If InStr(1, rngCell, str1) > 0 Then
        rngCell = Replace(rngCell.Value, str3, "")
    End If
        If InStr(1, rngCell, str1) > 0 Then
        rngCell = Replace(rngCell.Value, str4, "")
    End If
    Next rngCell
    End With
End Sub

错误是vba代码通过，但结果不存在。字符串中的字母仍然存在。

标签： vbaexcelletters

Answer 1

您需要一个.beforeColumns(6).CurrentRegion来引用Workbooks("xxxx.xlsx").Sheets("xxxx")：

With Workbooks("xxxx.xlsx").Sheets("xxxx")
    Set rngTemp = .Columns(6).CurrentRegion

否则，它指的是以下之一：

• Sheet代码所在的位置；
• ActiveSheet, 如果代码不在Sheet;