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

Question

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.

It is not working properly is not a real explanation of the issue ;) Can you say where the error occurs? What is not working? — Pierre44, Jul 17 '18 at 14:32
The script goes through, but the results are not there. The letters in the strings are still there — Cunneryn, Jul 17 '18 at 14:54

score 2 · Accepted Answer · answered Jul 17 '18 at 14:35

2

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:

the Sheet in which the code is located;
ActiveSheet, if the code is not inside a Sheet;

answered Jul 17 '18 at 14:35

Vityata

42,633
8
55
100

Thanks for that. I tried, but it still doesn't seem to work properly – Cunneryn Jul 17 '18 at 14:45
@Cunneryn - I guess you are having some empty cells in column 6, thus the `CurrentRegion` is not the best option. Try to write `MsgBox rngTemp.Address` exactly after the `Set rngTemp = .Col...` line and see whether this is the case. – Vityata Jul 17 '18 at 15:11
there definitely are empty cells in that column yes. is there another function that is more adequate for this situation? – Cunneryn Jul 17 '18 at 15:28
@Cunneryn - plenty of other ways. The best case is to locate the lowest cell and to define the range based on it - https://stackoverflow.com/questions/11926972/excel-vba-finding-the-last-column-with-data and https://www.rondebruin.nl/win/s9/win005.htm – Vityata Jul 17 '18 at 15:29
Thank you, I will try this method and hope its works – Cunneryn Jul 17 '18 at 15:33

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

1 Answers1