Jimbob2000
New Member
- Joined
- Jun 27, 2019
- Messages
- 25
I have the following code as part of a macro to reformat a spreadsheet when transferring information between two databases at a children's nonprofit where I work.
This part of the code is supposed to reorder the columns, and it works.... about half the time.
When it doesn't work, the debug function always highlights the row "Columns(counter).Insert Shift:=xlToRight" and it seems to get as far as moving the "Zip Code" column, but I don't understand this bit of code well enough to see what's going wrong. It always crashes excel when it doesn't work, which sucks.
Any help or suggestions would be gratefully received.
Thanks
This part of the code is supposed to reorder the columns, and it works.... about half the time.
When it doesn't work, the debug function always highlights the row "Columns(counter).Insert Shift:=xlToRight" and it seems to get as far as moving the "Zip Code" column, but I don't understand this bit of code well enough to see what's going wrong. It always crashes excel when it doesn't work, which sucks.
Any help or suggestions would be gratefully received.
Thanks
VBA Code:
Dim ColumnOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer
ColumnOrder = Array("Custom Case ID", "Referral Date", "Last Name", "First Name", "Zip Code", "Locator Info")
counter = 1
For ndx = LBound(ColumnOrder) To UBound(ColumnOrder)
Set Found = Rows("1:1").Find(ColumnOrder(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