none
VBA Ordering the String RRS feed

  • Question

  • Hi All,

    I have below code to create workbook name and i want to order the string as defined at the end of the post.


    Dim ws As Worksheet
        
        For Each ws In ActiveWorkbook.Worksheets
            If ws.CodeName Like "*wsTemp*" And Left(ws.Name, 1) <> "W" Then
        
            WbName = WbName & "_" & Left(ws.Name, 1) & "L"
            ElseIf ws.CodeName Like "*wsTemp*" And Left(ws.Name, 1) = "W" Then
        
            WbName = WbName & "_" & Left(ws.Name, 1) & "C"
            End If
        Next
        
            If WbName <> "" Then
            WbName = Right(WbName, Len(WbName) - 1)
            End If

    The above code gives wbName= "TL_GL_WC_AL

    I want it in this define order= WC_AL_GL_UL

    so if WC is found in string it shall always go first and if UL is found than it shall always go last as define above. It may happen that you may not find all or any of the above sring in wbname.

    Thanks,

    Zav


    Wednesday, January 6, 2016 7:13 PM

All replies

  • This is the forum to discuss questions and feedback for Excel for Developers, since your issue is relate to VBA, I'll move your question to the MSDN forum for Visual Basic for Applications (VBA)

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=isvvba

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents,
    and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us.

    Thanks for your understanding.

    Thursday, January 7, 2016 1:28 AM
  • The first workbook name entry will always start with a *L_ because the first if statement is a "does not equal W" logical.  So your first item cannot start with "WC" unless the first sheet in the sheets collection does, in fact, start with a "W."

    Might be helpful if you could give us at least the first letter of each sheet in your workbook in the same order.

    This might be something where you create a string that is delimited as you have it now and then split it into an array then perform a sort (either in VBA or on the sheet).

    Like this:

    Using your current code.  Append this to the end of it or call it.

    Sub SortString() Dim sWBName, sTemp As String Dim sSplitArray() As String Dim i, j As Integer sWBName = "UL_GL_WC_AL_TL_BC_KC" 'Hard code for debugging - use your "WbName" variable here instead. 'Divide sWBName into an array delimited by an underscore sSplitArray = Split(sWBName, "_") 'Empty the sWBName variable sWBName = Empty 'Sort Array (Bubble Sort Method) For i = LBound(sSplitArray) To UBound(sSplitArray) For j = LBound(sSplitArray) To UBound(sSplitArray) If Right(sSplitArray(j), 1) > Right(sSplitArray(i), 1) Then sTemp = sSplitArray(j) sSplitArray(j) = sSplitArray(i) sSplitArray(i) = sTemp End If Next j Next i 'Sort all "C" Entries in ascending order For i = LBound(sSplitArray) To UBound(sSplitArray) If Right(sSplitArray(i), 1) = "C" Then For j = LBound(sSplitArray) To UBound(sSplitArray) If Right(sSplitArray(j), 1) = "C" Then If Left(sSplitArray(i), 1) < Left(sSplitArray(j), 1) Then sTemp = sSplitArray(i) sSplitArray(i) = sSplitArray(j) sSplitArray(j) = sTemp End If End If Next j End If Next i 'Sort all "L" Entries in ascending order For i = LBound(sSplitArray) To UBound(sSplitArray) If Right(sSplitArray(i), 1) = "L" Then For j = LBound(sSplitArray) To UBound(sSplitArray) If Right(sSplitArray(j), 1) = "L" Then If Left(sSplitArray(i), 1) < Left(sSplitArray(j), 1) Then sTemp = sSplitArray(i) sSplitArray(i) = sSplitArray(j) sSplitArray(j) = sTemp End If End If Next j End If Next i 'After Array is sorted, spit back into a string variable using For-Next Loop For i = LBound(sSplitArray) To UBound(sSplitArray) sWBName = sWBName & sSplitArray(i) & "_" Next i 'Remove trailing "_" sWBName = Left(sWBName, Len(sWBName) - 1) 'Result to Immediate Window Debug.Print sWBName

    End Sub



    Thus, an input of "UL_GL_WC_AL_TL_BC_KC" results in an output of: "BC_KC_WC_AL_GL_TL_UL"

    Which I believe is what you were looking for.

    -G



    • Edited by Grasor Friday, January 8, 2016 4:02 PM
    Thursday, January 7, 2016 11:53 PM
  • Hi Grasor,

    The suggested code is too long and hard for me to understand. My original thread has the first and last letter of each worksheets (WC_AL_GL_UL).

    My code is creating string (workbook name) by taking first and last letter of each worksheet and putting underscore between each ws abbreviation. So for e.g i might have string UL, AL. So code shall create string like AL_UL. or i my code may generate wb name string like GL_UL_WC_AL than new code shall order the string like WC_AL_GL_UL

    Thanks,

    ZAV

    Friday, January 8, 2016 2:50 PM
  • Zav,


    The code I posted is just a 3 step sorting of the string you are receiving from your code.  It is as long as it needs to be to solve the problem, though I don't believe I'll be winning any brevity awards.  

    My understanding of your request is that you want all "*C_" sheet names toward the front of the workbook name and all "*L" sheet names toward the end.  Everything sorted in ascending order by the first letter.  I.e. WC_AL_GL_UL.

    The code I supplied should be able to be copied & pasted into your module at the end of your supplied code.  It can handle any length string you want to work with.

    Set

    sWBName = "UL_GL_WC_AL_TL_BC_KC"

    to 

    sWBName = wbName 'Your Variable

    'nothing else changes

    The output in the immediate window of the IDE (programming interface) should show the result.  If that works for you then you can remove/comment out the "debug.print.." line if you wish and then assign the sWBName variable to do what you need it to do.  If you can't see the immediate window press CTRL-G in the IDE.

    If you need more guidance please ask directly what it is about the supplied code that you do not understand and I will happily explain it.  If the result is incorrect then I need a different explanation of your desired result to assist you.

    Friday, January 8, 2016 4:01 PM