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-com
ffice
ffice" /><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
>
></o
>
<o
></o
>
End Sub<o
></o
>
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-com
Any help would be greatly appreciated. BTW, I am using Excel 2007.
Thank you,
Norbert
Sub Find_Move()<o
'<o
' Find_Move Macro<o
'<o
Range("A1").Select<o
<o
Dim MyArr As Variant<o
Dim ColArr As Variant<o
Dim TargetCol As Long<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
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
For i = LBound(MyArr) To UBound(MyArr)<o
TargetCol = Cells.Find(What:=MyArr(i)).Column
Columns(TargetCol).Cut Destination:=Columns(ColArr(i))<o
></o
>
Next<oColumns(TargetCol).Cut Destination:=Columns(ColArr(i))<o
<o
End Sub<o