Posts by dxstudios

    Hi Friends,

    i have task to replace some particular Text in a range of cells, i have a existing code, which replaces text properly,

    but now my ask is just to replace the Text only at the lastpart of the Text,

    it should not replace the text in the start Text / Middle Text, it should get replaced only at the End.

    ActiveSheet.Range("A:A").Replace " LAKE", " LK"
    ActiveSheet.Range("A:A").Replace " ROAD", " RD"
    ActiveSheet.Range("A:A").Replace " STREET", " ST"
    ActiveSheet.Range("A:A").Replace " DRIVE", " DR"

    for example:

    existing code converts like :

    Lakeshore Road to LKshore RD

    code required:

    Lakeshore Road to Lakeshore RD


    sorry, its my mistake ....i should have share this files earlier itself..

    actually i thought like Existing code provided by you will work for Vendor 2 type files also.....but no luck

    let me explain the Vendor 2 type files,

    Here in B:B range, name of the sheets are mentioned as mix of Alphabetic characters like NNIM, ARDR-2, LWIS, Sheet1.... etc.,

    i tried with the existing code, it consider only the Sheet1 from the above list, rest of the sheets names are getting hided.

    in earlier files sheet names are mentioned as Sheet1,Sheet2,SheetA,.... etc.,

    so its working fine,

    Now i need to make it work on the Vendor2 Type it should consider that mix of Alphabetical characters also ...

    Sir, i have tried modifiying the code as per your suggestion,

    Actually i have 2 type of files (Vendor 1 & Vendor 2)

    it works fine for the Vendor 1 files, but not working on the Vendor 2 type files....

    here in Vendor 2 files, everything same as vendor 1, only the terminology changes.

    attaching both type of files for your reference Sir



    If x = 0 And sh.Name <> "SheetA-Approved" Then sh.Visible = xlSheetVeryHidden

    Sir, as "SheetA-Approved" is added in the code, now its considering & working fine Sir,

    but actually the problem is ....i have many cases like this.....

    (sometimes it comes as SheetA-Appr, SheetB- correct, Pending SheetA-Appr)

    the red texts are the additional part of the sheet names,

    so i m requesting for code to search for Approx Match Sir

    as per Mr.Dave suggestion, i used this line, but not happening...

    x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), "*" & s & "*")

    No Sir, as per the attached sample sheet in above thread,

    code should keep the sheets (SheetA-Approved,SheetB,SheetC and Sheet1) & rest of the sheets has to be Hidded / Deleted.

    but after running code, it keeps the following sheets only (SheetB,SheetC and Sheet1)

    SheetA-Approved is not considered as its not exactly matching...

    i m requesting solution for this issue Sir.

    Then I need to see that code

    this is the code i m using it in personal workbook Sir

    For Each sh In Sheets
     s = sh.Name
     If sh.Name <> ws.Name Then
     x = Application.WorksheetFunction.CountIf(ws.Range("B:B"), s)
     If x = 0 Then sh.Delete
     End If
     Next sh


    Here the code looks for the sheet name with Exact names in range (B:B)

    is it possible to find the approximate matches?

    for example:

    if the name appears as Sheet1 in B:B & sheet name comes as Sheet1-Approved

    since the sheet name contains additional words (-Approved) in its names, code not considering it.

    Please Help me Sir.