Consulting

Results 1 to 6 of 6

Thread: VBA - what is Chr(1)

  1. #1
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location

    VBA - what is Chr(1)

    Hi Team,

    Need your help, in below code what is Chr(1), what string will come in its place if want to replace. Thanks in advance

    Dim Arr As Variant
      Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
            Join(Application.Transpose(Sheet2.Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
            Join(Application.Transpose(Sheet3.Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))


    Regards,
    mg

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello malleshg24,

    Having a reference to what the string is being used for would be helpful. In the original ASCII table character 1 represented the start of a header.

    I suspect a typo here. Chr(10) or line feed would make more sense.
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by malleshg24 View Post
    Hi Team,

    Need your help, in below code what is Chr(1), what string will come in its place if want to replace. Thanks in advance

    Dim Arr As Variant
    Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet2.Range("C2", Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet3.Range("E2", Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))



    Regards,
    mg

    Since there's Joins and Splits there, I suspect that Chr(1) is being used as a special marker character (I've used it like that many times) that won't be in regular text

    Simple demo, broken into bite-size pieces

    Option Explicit
    
    
    Sub JoinSplit()
        Dim A1 As Variant, A2 As Variant, A3 As Variant, A0 As Variant
        Dim s1 As String, s2 As String, s3 As String, s0 As String
        Dim i As Long
        
        A1 = Array("A1", "B1", "C1", "D1")
        A2 = Array("A2", "B2", "C2", "D2")
        A3 = Array("A3", "B3", "C3", "D3")
    
    
        s1 = Join(A1, Chr(1))
        s2 = Join(A2, Chr(1))
        s3 = Join(A3, Chr(1))
    
    
        s0 = s1 & Chr(1) & s2 & Chr(1) & s3
    
    
        A0 = Split(s0, Chr(1))
        
        For i = LBound(A0) To UBound(A0)
            Debug.Print i & " -- " & A0(i)
        Next i
    
    
    End Sub
    Output -- the Chr(1)'s are not in the array -- I'd leave them alone

    0 -- A1
    1 -- B1
    2 -- C1
    3 -- D1
    4 -- A2
    5 -- B2
    6 -- C2
    7 -- D2
    8 -- A3
    9 -- B3
    10 -- C3
    11 -- D3
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Looks to me like the Chr(1)'s are the DELIMITERS for the three JOINs and the SPLIT

    Using Chr(1), means that the various strings in the various Ranges will not SPLIT on any keyboard character.

    Also, Unicode safe.

    Inputs
    A
    B C D E
    A D049 H-
    B E,.: I
    C F G J

    OutPut:
    ABCD049E,.:F GH-IJ
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    malleshg24, I tried your code and got an error Method range of object '_Worksheet' failed, which is due to unqualified references to Cells.
    This doesn't error:
    Arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))
    You can shorten the line with:
    Arr = Split(Application.TextJoin(Chr(1), False, _
    Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp)), _
    Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp)), _
    Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1))
    with the added bonus of ignoring blank cells by changing False to True.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    Banned VBAX Contributor
    Joined
    Aug 2017
    Posts
    144
    Location
    Hi Team,

    Thanks you all for your help and good information on it.
    Below code works for me, I take all data into range, and after that filter in array,

    Below line works, it filter single string and give result.
    filteredArray = VBA.Filter(Application.Transpose(arr), "completed", True)

    one more question can we filter two strings in vba.filter this line?....
    like :=> Array("completed","In Progress")

    Sub Test()
    Dim arr As Variant
    Dim filteredArray() As String
    arr = Split(Join(Application.Transpose(Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet2.Range("C2", Sheet2.Cells(Rows.Count, "C").End(xlUp))), Chr(1)) & Chr(1) & _
    Join(Application.Transpose(Sheet3.Range("E2", Sheet3.Cells(Rows.Count, "E").End(xlUp))), Chr(1)), Chr(1))
    filteredArray = VBA.Filter(Application.Transpose(arr), "completed", True)
    MsgBox UBound(filteredArray) + 1
    End Sub
    Thanks
    mg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •