+ Reply to Thread
Results 1 to 11 of 11

VBA Code For Nested If/Column Reorder

  1. #1
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    VBA Code For Nested If/Column Reorder

    Hi All,

    I am working on a large scale project where I receive a report that often comes in different formats each time. One time the columns may be in one order, other times it will be in a different order and I have to import it into a sheet where the order has to be the same every time. I have a very small background in coding in Java, but not so much with VBA. I have uploaded a test document of what I am trying to do in a small scale example. The first worksheet - "Correct Order" is the order that I need to final result to be in. The second worksheet - "Incorrect order" is one example of how the data may come in to me.

    Below, I am going to try to write out logically what it is I am trying to accomplish, and I am hoping that someone with far better VBA coding skills than I have can help me with the syntax. Once I get this in small scale, I should be able to replicate in large scale. Thank you in advance for all of your help!

    What I am trying to do written out in a logical manner using the attached spreadsheet as a reference:

    If (A1 = "Name") - Do nothing and proceed to checking B1 for next condition
    If (A1 <> "Name") - Find the column that begins with "Name" and move to Column A
    If ("Name" does not exist in any of the column headers)- insert a column in Column A named "Name"

    If(B1 = "Date")- Do nothing and proceed to checking C1 for next condition
    If (B1 <> "Date") - Find the column that begins with "Date" and move to Column B
    If ("Date" does not exist in any of the column headers)- insert a column in Column B named "Date"

    And so on through all of the columns.

    Again, thank you very much for your help!
    Attached Files Attached Files
    Last edited by Yoandsm; 08-21-2019 at 07:08 PM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA Code For Nested If/Column Reorder

    Try
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    Re: VBA Code For Nested If/Column Reorder

    Hi jindon, I believe this worked! Thank you so much. I am going to apply this to my much larger spreadsheet and see if it still works. Thanks again!!

  4. #4
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    Re: VBA Code For Nested If/Column Reorder

    Hi jindon, I have a follow up question for you. I'm hoping this will be an easy fix. Something breaks down when there is more than one column that needs to be inserted. For example, I have updated my original sheet that I attached. If you run the code in the "Incorrect Order" sheet, you will see that instead of having A1, B1, and C1, be the missing column names, it adds three columns, but it lists the column names going in A1, A2 and A3 instead.
    Any thoughts on this?

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA Code For Nested If/Column Reorder

    Column A:C are fixed.
    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Hi ! Try this !


    As a starter :

    PHP Code: 
    Sub Demo1()
             
    Dim HV
        
    For Each H In [{"Email Address","Phone Number","Date","Name"}]
            
    With ActiveSheet.UsedRange
                 V 
    Application.Match(H, .Rows(1), 0)
                 If 
    IsNumeric(VThen If 1 Then .Columns(V).Cut: .Columns(1).Insert
            End With
        Next
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  7. #7
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    Re: VBA Code For Nested If/Column Reorder

    Hi jindon, thank you for the quick response! It looks like when I use this on the attached sheet, it inserts the columns into the middle of the data instead of in the order that it is typed into the array. For example, if I change the Array to be

    Please Login or Register  to view this content.
    Then the output becomes "Phone Number", "Date", "Col 1", "Col 2", "Col 3", "Email Address".

    I have tried to play around with it, but every time I adjust the code, it gives me an error.

    Thank you for all of your help! And if you don't have any more time to work on it, I completely understand.

    Thanks again

  8. #8
    Registered User
    Join Date
    02-08-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    77

    Re: Hi ! Try this !

    Hi Marc, unfortunately this didn't work. If I added new items in the Array, it did not add them to the sheet. I will keep playing with it though. Thank you for the help!

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,835

    Re: VBA Code For Nested If/Column Reorder

    Then just use the first one with the new array...
    Please Login or Register  to view this content.

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Hi ! Try this !

    (removed as misreading)
    Last edited by Marc L; 08-21-2019 at 11:39 PM.

  11. #11
    Registered User
    Join Date
    08-05-2019
    Location
    New Delhi
    MS-Off Ver
    2013 and Office 365
    Posts
    14

    Re: Hi ! Try this !

    Hoping this would be related somehow...

    I am very new to VBA and MACROS in Excel and need some help.

    I am trying to combine the Vlookup and VBA or Macro and create a query so that if i select a number in a cell the corresponding data/information should show as a comment in the same cell.

    Would appreciate help from anyone and everyone.

    Thanks you in advance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to reorder match columns on multiple sheets to a column on another sheet
    By barman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-10-2018, 06:48 PM
  2. Replies: 0
    Last Post: 01-27-2016, 11:19 AM
  3. [SOLVED] Reorder Rows and Sum column totals given specified criteria
    By bwmuhich in forum Excel General
    Replies: 5
    Last Post: 04-09-2014, 03:58 PM
  4. Formula to sort column and reorder in new column
    By RCope in forum Excel General
    Replies: 4
    Last Post: 03-27-2012, 11:31 AM
  5. Replies: 3
    Last Post: 06-16-2010, 04:23 PM
  6. Reorder a column into flat format
    By gvelix in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-06-2009, 08:42 AM
  7. Change Listbox Sheet reorder code
    By Stuart in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2005, 03:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1