Trying to rearrange columns with a macro in excel 2010

[hawkster]

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

 

[Momentman]

On what line do you get the error? the line would normally be highlighted in yellow

 

[shg]

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

 

[hawkster]

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

 

[hawkster]

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?

 

[Momentman]

Inum was not defined. Whats the initial value of inum? if undefined, Inum is probably nothing as posted by shg

 

[hiker95]

hawkster,

See the link for two macros that work - you would be interested in the first macro:
http://www.mrexcel.com/forum/excel-...k-column-when-value-missing-header-array.html

 

[hawkster]

hawkster,

See the link for two macros that work - you would be interested in the first macro:
http://www.mrexcel.com/forum/excel-...k-column-when-value-missing-header-array.html

Wow, thanks hiker95, it worked an absolute treat!

 

[hiker95]

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).

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.

 

[hiker95]

hawkster,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.