VBA Find - Move macro

[Norbert]

Hello,

I have an Excel file that has columns in random order. The order I would like to have them in is listed under 'MyArr.' The problem is that not all of the column names which are listed in 'MyArr' are in the file. They are also in random order.

The macro below seems to be a good solution. However, when I run it I always get a “Runtime Error ’91’: Object Variable or With block variable not set.” The error occurs on the line highlighted in red(TargetCol = Cells.Find(What:=MyArr(i)).Column).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o> I think that I might need some error checking in the case it doesn’t find the column. If it doesn't find the column, I want to insert a blank column.

Any help would be greatly appreciated. BTW, I am using Excel 2007.

Thank you,
Norbert




Sub Find_Move()<o></o>
'<o></o>
' Find_Move Macro<o></o>
'<o></o>
Range("A1").Select<o></o>
<o></o>
Dim MyArr As Variant<o></o>
Dim ColArr As Variant<o></o>
Dim TargetCol As Long<o></o>
<o></o>

MyArr = Array("StudentID[0]", "Term[0]", "Prefix[0]", "Last_Name[0]", "First_Name[0]", "Gender[0]", "Date_of_Birth[0]", "Home_address[0]", "City[0]", "State[0]", "ZIP[0]", "Country[0]", "Phone_1[0]", "Phone_2[0]", "E-Mail[0]", "Special_Needs[0]", "Medical_Conditions[0]", "Emergency_Contact_Name[0]", "Relationship[0]", "Address1[0]", "Address2[0]", "Phone_Number[0]", "Hall[0]", "GlobalLivingCommunity[0]", "HealthyLifestylesCommunity[0]", "QuietHours[0]", "Roommate_1ID[0]", "Roommate_1[0]", "Roommate_2ID[0]", "Roommate_2[0]", "Roommate_3ID[0]", "Roommate_3[0]", "Roommate_4ID[0]", "Roommate_4[0]", "WakeUpEarly[0]", "GoToBedLate[0]", "StudyMorning[0]", "StudyAfternoon[0]", "StudyNight[0]", "StudyWith[0]", "MyRoomIsMostly[0]", "Smoker[0]", "My_Academic_Program_Major[0]", "HobbiesAndComments[0]", "MealPlan[0]", "HealthConcern[0]", "SignatureDate[0]", "TermsAndConditions[0]", "Theft[0]", "Obligation[0]", "AgreeToTerms[0]", "LIU-Student[0]") <o></o>


ColArr = Array("A:A", "B:B", "C:C", "D:D", "E:E", "F:F", "G:G", "H:H", "I:I", "J:J", "K:K", "L:L", "M:M", "N:N", "O:O", "P:P", "Q:Q", "R:R", "S:S", "T:T", "U:U", "V:V", "W:W", "X:X", "Y:Y", "Z:Z", "AA:AA", "AB:AB", "AC:AC", "AD:AD", "AE:AE", "AF:AF", "AG:AG", "AH:AH", "AI:AI", "AJ:AJ", "AK:AK", "AL:AL", "AM:AM", "AN:AN", "AO:AO", "AP:AP", "AQ:AQ", "AR:AR", "AS:AS", "AT:AT", "AU:AU", "AV:AV", "AW:AW", "AX:AX", "AY:AY", "AZ:AZ", "BA:BA", "BB:BB", "BC:BC", "BD:BD", "BE:BE", "BF:BF", "BG:BG", "BH:BH") ' Destination column
<o></o>
<o></o>

For i = LBound(MyArr) To UBound(MyArr)<o></o>

TargetCol = Cells.Find(What:=MyArr(i)).Column
Columns(TargetCol).Cut Destination:=Columns(ColArr(i))<o></o>
​

Next<o></o>
<o></o>
End Sub<o></o>

 

[VoG]

Perhaps like this

Sub Find_Move()
'
' Find_Move Macro
'
Range("A1").Select

Dim MyArr As Variant
Dim ColArr As Variant
Dim TargetCol As Long
Dim Found As Range

MyArr = Array("StudentID[0]", "Term[0]", "Prefix[0]", "Last_Name[0]", "First_Name[0]", "Gender[0]", "Date_of_Birth[0]", "Home_address[0]", "City[0]", "State[0]", "ZIP[0]", "Country[0]", "Phone_1[0]", "Phone_2[0]", "E-Mail[0]", "Special_Needs[0]", "Medical_Conditions[0]", "Emergency_Contact_Name[0]", "Relationship[0]", "Address1[0]", "Address2[0]", "Phone_Number[0]", "Hall[0]", "GlobalLivingCommunity[0]", "HealthyLifestylesCommunity[0]", "QuietHours[0]", "Roommate_1ID[0]", "Roommate_1[0]", "Roommate_2ID[0]", "Roommate_2[0]", "Roommate_3ID[0]", "Roommate_3[0]", "Roommate_4ID[0]", "Roommate_4[0]", "WakeUpEarly[0]", "GoToBedLate[0]", "StudyMorning[0]", "StudyAfternoon[0]", "StudyNight[0]", "StudyWith[0]", "MyRoomIsMostly[0]", "Smoker[0]", "My_Academic_Program_Major[0]", "HobbiesAndComments[0]", "MealPlan[0]", "HealthConcern[0]", "SignatureDate[0]", "TermsAndConditions[0]", "Theft[0]", "Obligation[0]", "AgreeToTerms[0]", "LIU-Student[0]")


ColArr = Array("A:A", "B:B", "C:C", "D:D", "E:E", "F:F", "G:G", "H:H", "I:I", "J:J", "K:K", "L:L", "M:M", "N:N", "O:O", "P:P", "Q:Q", "R:R", "S:S", "T:T", "U:U", "V:V", "W:W", "X:X", "Y:Y", "Z:Z", "AA:AA", "AB:AB", "AC:AC", "AD:AD", "AE:AE", "AF:AF", "AG:AG", "AH:AH", "AI:AI", "AJ:AJ", "AK:AK", "AL:AL", "AM:AM", "AN:AN", "AO:AO", "AP:AP", "AQ:AQ", "AR:AR", "AS:AS", "AT:AT", "AU:AU", "AV:AV", "AW:AW", "AX:AX", "AY:AY", "AZ:AZ", "BA:BA", "BB:BB", "BC:BC", "BD:BD", "BE:BE", "BF:BF", "BG:BG", "BH:BH") ' Destination column



For i = LBound(MyArr) To UBound(MyArr)

    Set Found = Cells.Find(What:=MyArr(i))
    If Not Found Is Nothing Then
        TargetCol = Found.Column
        Columns(TargetCol).Cut Destination:=Columns(ColArr(i))
    Else
        'action if header not found
    End If

Next

End Sub

 

[Norie]

Try this.

Set rngFnd = Cells.Find(What:=MyArr(i))
 
If rngFnd Is Nothing
    ' header not found
   Columns(ColArr(i)).EntireRow.Insert
Else
   Columns(rngFnd.Column).Cut Destination:=Columns(ColArr(i))
End If

 

[Norbert]

Thanks Peter. I used a combination of Norie's and your reply and it worked like a charm. Thanks again for the quick reply.

 

[Norbert]

Thanks Norie. I used a combination of Peter's and your reply and it worked like a charm. Thanks again for the quick reply.