vbaexcelletters

How to remove modify a string to remove letters from a new worksheet

So I've been trying to create a Macro to format the open a new workfile and then modify 1 colomn to remove the letters from the string of number (20180717a to become 20180717 for example). I've tried several things and came up with this code, but it doent seem to work properly:

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

The error is that the vba code goes through, but the results are not there. The letters in the strings are still there.

Solution

  • You need a . before Columns(6).CurrentRegion to refer to the Workbooks("xxxx.xlsx").Sheets("xxxx"):

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

    Otherwise it is referring to one of the following: