Results 1 to 10 of 10

Thread: Trying to rearrange columns with a macro in excel 2010
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Trying to rearrange columns with a macro in excel 2010

    Hi All,

    I am trying to rearrange the order of columns A to K with a macro, I have copied the following from somewhere else and has worked on a different sheet, but I am now getting a

    "Run time error 91 - object variable or with bock variable not set" -

    each time i run it on the current one. I am not familiar with VBA, any help is very much appreciated.

    Dim v As Variant, x As Variant, findfield As Variant
    Dim oCell As Range
    Dim iNum As Long
    v = Array("EMPLOYER", "NAME", "FAMILY NAME", "FIRST NAME", "CLAIM ID", "PAID FROM", "PAID TO", "CHQ DRAWN DATE", "AMOUNT PAID", "PAYMENT TYPE", "PAYEE")
    For x = LBound(v) To UBound(v)
    findfield = v(x)
    iNum = iNum + 1
    Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not oCell.Column = iNum Then
    Columns(oCell.Column).Cut
    Columns(iNum).Insert Shift:=xlToRight
    End If
    Next x

    Cheers Hawkster

  2. #2
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,962
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    On what line do you get the error? the line would normally be highlighted in yellow
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  3. #3
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    I expect it means the value was not found, so oCell is Nothing.

  4. #4
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    Sorry, it is the line that is underlined -If Not oCell.Column = iNum Then

  5. #5
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    Quote Originally Posted by shg View Post
    I expect it means the value was not found, so oCell is Nothing.
    Sorry, I only started macros yesterday and literally copied it from another web page, does that mean I can delete it from the code or do I need to set a new value somewhere?

  6. #6
    Board Regular Momentman's Avatar
    Join Date
    Jan 2012
    Location
    Nigeria
    Posts
    3,962
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    Inum was not defined. Whats the initial value of inum? if undefined, Inum is probably nothing as posted by shg
    Using Excel 2007 ,2010,2013 Windows 7 - 64bit

    You can be whatever you think you can

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    hawkster,

    See the link for two macros that work - you would be interested in the first macro:
    http://www.mrexcel.com/forum/excel-q...der-array.html
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  8. #8
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    Quote Originally Posted by hiker95 View Post
    hawkster,

    See the link for two macros that work - you would be interested in the first macro:
    http://www.mrexcel.com/forum/excel-q...der-array.html
    Wow, thanks hiker95, it worked an absolute treat!

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    hawkster,

    I put your array elements in the below macro.


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Code:
    Option Explicit
    Sub ReorderColumnsV2()
    ' hiker95, 12/30/2013
    ' http://www.mrexcel.com/forum/excel-questions/747549-trying-rearrange-columns-macro-excel-2010-a.html
    Dim arrColOrder As Variant, ndx As Integer
    Dim Found As Range, counter As Integer
    
    arrColOrder = Array("EMPLOYER", "NAME", "FAMILY NAME", "FIRST NAME", "CLAIM ID", "PAID FROM", "PAID TO", "CHQ DRAWN DATE", "AMOUNT PAID", "PAYMENT TYPE", "PAYEE")
    
    counter = 1
    Application.ScreenUpdating = False
    For ndx = LBound(arrColOrder) To UBound(arrColOrder)
      Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
      If Not Found Is Nothing Then
        If Found.Column <> counter Then
           Found.EntireColumn.Cut
           Columns(counter).Insert Shift:=xlToRight
           Application.CutCopyMode = False
        End If
        counter = counter + 1
       End If
    Next ndx
    Application.ScreenUpdating = True
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the ReorderColumnsV2 macro.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,645
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Trying to rearrange columns with a macro in excel 2010

    hawkster,

    Thanks for the feedback.

    You are very welcome. Glad I could help.

    And, come back anytime.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

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
  •