Trying to rearrange columns with a macro in excel 2010

hawkster

New Member
Joined
Dec 30, 2013
Messages
4
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
On what line do you get the error? the line would normally be highlighted in yellow
 
Upvote 0
I expect it means the value was not found, so oCell is Nothing.
 
Upvote 0
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?
 
Upvote 0
Inum was not defined. Whats the initial value of inum? if undefined, Inum is probably nothing as posted by shg
 
Upvote 0
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.
 
Upvote 0
hawkster,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top