Row match and alignment VBA code
Results 1 to 2 of 2

Thread: Row match and alignment VBA code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Row match and alignment VBA code

    I have a vba code I use to align and match column rows of data and up to about200 rows of data it's been working without any problems. However, I haveencountered a problem when using it on several hundred or thousands of rows of data.

    Below is the code I am using to match and align the column rows of data: -

    Sub RowFormat()
    Dim Rng As Range
    Dim Dn As Range
    Dim Dic1 As Object
    Set Rng = Range(Range("A2"), Range("A" &Rows.Count).End(xlUp)).Resize(, 2)
    Set Dic1 = CreateObject("scripting.dictionary")
    Dic1.CompareMode =vbTextCompare
    For Each Dn In Rng
    If Not Dic1.Exists(Dn.Value) Then
    Dic1.Add Dn.Value,""
    Else
    Dic1.Remove (Dn.Value)
    End If
    Next

    For Each Dn In Rng
    If Dn <> "" AndDic1.Exists(Dn.Value) Then
    If Dn.Column = 1 Then
    Dn.Offset(,1).Insert
    Else
    Dn.Offset(, -1).Insert
    End If
    End If
    Next Dn
    Set Rng = Range(Range("A2"), Range("A" &Rows.Count).End(xlUp))
    For Each Dn In Rng
    If Not Dn = "" And NotDic1.Exists(Dn.Value) Then Dn.Offset(, 1) = Dn
    Next Dn
    EndSub

    Thedata below is an example of the raw data with both columns sorted in ascendingorder before running the code.

    A-0001-T-01 A-0002-T-02
    A-0002-V-01 A-0002-V-02
    AH-01-V-0001 AX-00001
    B-01-RG-01 C-0002
    E-00001A E-00002A
    E-00002A

    Below is the data after running the code, as you can see the second column hasbeen aligned to match the first column and visa versa.
    If there is no match it leaves a blank cell and goes onto the next row of data

    A-0001-T-01
    A-0002-T-02
    A-0002-V-01
    A-0002-V-02
    AH-01-V-0001
    AX-00001
    B-01-RG-01
    C-0002
    E-00001A
    E-00002A E-00002A

    However, a problem has occurred with the code when used on bigger amounts ofdata and below shows the match and alignment has failed for the rows 458 thru'462, It then continues to correctly match and align the rows of datathereafter.

    A further problem due to the miss-match in the second column, the data hasincreased by 5 items, re the duplication of V-0001 thru' V-0005, as shownbelow in red font.


    US-00-154
    US-00-155
    V-0001
    US-00-156
    V-0002
    US-00-157
    V-0003
    US-00-158
    V-0004
    US-00-162
    V-0005
    V-0001 V-0001
    V-0002 V-0002
    V-0003 V-0003
    V-0004 V-0004
    V-0005 V-0005
    V-0006 V-0006
    V-0007 V-0007
    X-0001
    X-0002

    I would be grateful for any advice how to remedy this problem, or if there is a better wayto meet my objective consistently.

    Thx

    Amms123
    0 0
     

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,499
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Row match and alignment VBA code

    Duplicate to https://www.mrexcel.com/forum/excel-...-vba-code.html

    Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #12 of the Forum Rules and points 6 & 7 of the Forum Use Guidelines.

    Any bumps, clarifications, or follow-ups should be posted to the linked thread.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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