Search multiple strings and replaced across multiple worksheets

[dellzy]

Hi Experts,

Below is what I've tried to do across multiple worksheets in same workbook (except for Sheet1). The code supposed to insert additional column in each wsheet and put the wsheet name to all rows with data. Then after that need to find strip off the strings "N-" or "AT-" in that first column. It does correctly for inserting the wsheet name but it doesn't do anything on the find & replace strings. And I notice that the highlighted range also not cleared (supposed to be cleared before selecting the whole col A and do the find and replace). But I don't see pop-up error too. Please help to correct my coding below:-

Sub InsertSheetNameAll()
Dim LRow As Integer
Dim i As Integer
Dim WS_count As Integer
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

If ws.Index > 1 Then
ws.Columns(1).Insert
ws.Cells(1, 1) = "CTT#"
ws.Cells(2, 1) = ws.Name

    LRow = ws.Range("B1", ws.Range("B1").End(xlDown)).Rows.Count
    'MsgBox LRow
    ws.Range("A2").Copy
    ws.Range("A3:A" & LRow).PasteSpecial Paste:=xlValues
 Columns("A:A").Select
    Selection.Replace What:="N-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="AT-", Replacement:="", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
    Range("A1").Select
    
Application.CutCopyMode = False
    
End If

    Application.CutCopyMode = False
Next

End Sub

Thank you in advance.

DZ

 

[Fishboy]

Hi Experts,

Below is what I've tried to do across multiple worksheets in same workbook (except for Sheet1). The code supposed to insert additional column in each wsheet and put the wsheet name to all rows with data. Then after that need to find strip off the strings "N-" or "AT-" in that first column. It does correctly for inserting the wsheet name but it doesn't do anything on the find & replace strings. And I notice that the highlighted range also not cleared (supposed to be cleared before selecting the whole col A and do the find and replace). But I don't see pop-up error too. Please help to correct my coding below:-

Sub InsertSheetNameAll()
Dim LRow As Integer
Dim i As Integer
Dim WS_count As Integer
Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets

If ws.Index > 1 Then
ws.Columns(1).Insert
ws.Cells(1, 1) = "CTT#"
ws.Cells(2, 1) = ws.Name

    LRow = ws.Range("B1", ws.Range("B1").End(xlDown)).Rows.Count
    'MsgBox LRow
    ws.Range("A2").Copy
    ws.Range("A3:A" & LRow).PasteSpecial Paste:=xlValues
 Columns("A:A").Select
    Selection.Replace What:="N-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="AT-", Replacement:="", LookAt:=xlPart, _
       SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
       ReplaceFormat:=False
    Range("A1").Select
    
Application.CutCopyMode = False
    
End If

    Application.CutCopyMode = False
Next

End Sub

Thank you in advance.

DZ

Hi DZ,

One thing that strikes me is that right at the start of the code you are inserting a column ws.Columns(1).Insert which is basically making a new column A is it not? This new column A is blank, so presumably all subsequent searches and / or copying you are doing in column A is not working as the column is empty.

 

[dellzy]

Hi,

Actually, if you scroll through the macro down after the insert column(1), it is putting the sheet name into that new column, in every row until last row that contain data. So, after it's doing that, then it supposed to search the certain strings in that column and strip off/trim, which the part my coding doesn't give any result but no error pop-up as well.

Thanks.

DZ

 

[Fishboy]

Hi,

Actually, if you scroll through the macro down after the insert column(1), it is putting the sheet name into that new column, in every row until last row that contain data. So, after it's doing that, then it supposed to search the certain strings in that column and strip off/trim, which the part my coding doesn't give any result but no error pop-up as well.

Thanks.

DZ

Hi again DZ, my apologies. Now that I have actually tested your code out I understand what it is doing properly. Strangely enough in your OP you mention that the find / replace sections of your code aren't doing anything, however in my tests it seems to be working fine.

To test it I made a dummy document with about 10 sheets. All sheets after sheet1 I renamed to "N- TheRelevantSheetNumber AT-"

Next I looked at your macro in the VBA developer window and started stepping through the code one line at a time with the F8 key, periodically checking the workbook to watch the changes. I can confirm that your code did indeed find and replace all instances of N- and AT- in column A of each sheet and replace them with "". If it is definitely not working as expected at your end, are you able to share a copy of your workbook with us to check? You would need to upload it to something like Drop Box or One Drive etc then share a link to the file here in a forum post.

 

[dellzy]

Hi again DZ, my apologies. Now that I have actually tested your code out I understand what it is doing properly. Strangely enough in your OP you mention that the find / replace sections of your code aren't doing anything, however in my tests it seems to be working fine.

To test it I made a dummy document with about 10 sheets. All sheets after sheet1 I renamed to "N- TheRelevantSheetNumber AT-"

Next I looked at your macro in the VBA developer window and started stepping through the code one line at a time with the F8 key, periodically checking the workbook to watch the changes. I can confirm that your code did indeed find and replace all instances of N- and AT- in column A of each sheet and replace them with "". If it is definitely not working as expected at your end, are you able to share a copy of your workbook with us to check? You would need to upload it to something like Drop Box or One Drive etc then share a link to the file here in a forum post.

Strange to know that you run it successfully at your end. Alright then. I have shared it in dropbox. Below is the URL:-

https://www.dropbox.com/s/qxbfcee3twaqnrs/Compiler.xlsb?dl=0

 

[dellzy]

Sorry, please ignore the earlier URL and accept below instead :-

https://www.dropbox.com/s/qxbfcee3twaqnrs/Compiler.xlsb?dl=0

 

[Fishboy]

Strange to know that you run it successfully at your end. Alright then. I have shared it in dropbox. Below is the URL:-

https://www.dropbox.com/s/qxbfcee3twaqnrs/Compiler.xlsb?dl=0

Hmm, your file is definitely behaving and the macro is doing exactly what it says on the tin.

Fortunately I have the luxury of 2 monitors so I can have the workbook open on one screen and the VBA window open on the other. Doing so allows me to step through the code with the F8 key and watch the procedure unfold on the other screen.

For example, the first "named" sheet (i.e not Sheet1) is called N-1-72632340633. Running your macro inserts a column at the front, puts CTT# in A1, then from A2 down to the end it puts N-1-72632340633. When the next part of your macro kicks in the values in column A change from N-1-72632340633 to 1-72632340633. The macro then moves on to the next sheet.

I am a little confused why it works at this end, even using a copy of your exact workbook, but does not work at your end. Do you have any other macros that are misbehaving or is everything else working as intended?

 

[dellzy]

Hi Fishboy,

Very sorry for the delays. I read your reply much earlier but since it's still not working at my end, I decided to seek from other forum then got other modified coding then tried out and works. Hence, I thank you for your effort to try to help me out but I decided to just use the modified coding and this considered solved for me already.

Thank you and sorry for any inconvenience.

DZ

 

[Fishboy]

Hi Fishboy,

Very sorry for the delays. I read your reply much earlier but since it's still not working at my end, I decided to seek from other forum then got other modified coding then tried out and works. Hence, I thank you for your effort to try to help me out but I decided to just use the modified coding and this considered solved for me already.

Thank you and sorry for any inconvenience.

DZ

No worries, I am pleased you managed to get it sorted.

 

[Dazzawm]

Fishboy you need to delete some messages in your inbox as I cant reply to your PMs!