Macro to perform changes without cell location references

Hi,
I receive an Excel file with data on a daily basis and do several changes to the file before it can be used. I want to create a macro to do these changes since they are always the same changes.
I could just record this macro but the problem is that the file doesn’t always contain the same columns so my macro cannot include any column or cell location references which is why I am seeking help on the best Excel forum around.

I have attached an example file and here are the changes I need the macro to make without referencing a column and in turn any specific cells:
1. Mark the text on the first row. (Meaning the first populated cell until and including the last)
2. Choose “Find & Select” -> “Go To Special” -> “Blanks” to mark all blank cells on the row. These empty cells represent empty columns.
3. Choose “Delete…” and then to remove “Entire column” to get rid of the blank columns.
4. Auto-adjust the column width of all text columns.
5. Change the column width to 39,00 (278 pixels) for the columns “Description”, “Resolution” and “Detailed_Description”.
6. After the columns “Submit_Date” and “Last_Resolved_Date”, insert a new column and on the first row name them “Submit_Time” and “Last_Resolved_Time” respectively.
7. Mark all text in the column “Submit_Date” except for the titel.
8. Find & Replace “ “ (double space) with “,”. (This is what I do to most easily use “Text to Columns”)
9. Use “Text to Columns” on the “Submit_Date” column except for the title and separate the date and time so that the time values are put in the new “Submit_Time” column.
10. Format all cells from row 2 in the “Submit_Date” and “Submit_Time” columns to display type “Date” and “Time” respectively.
11. Repeat steps 7-10 on the column “Last_Resolved_Date”.
12. In the last two cells on row 1 enter “Days_Out” and “Days_Range” respectively.
13. In the first cell on row 2 in the “Days_Out” column enter the following formula:
=IF(C2="Closed";"";IF(C2="Cancelled";"";IF(C2="Resolved";"";TODAY()-E2)))
Also format the cell as “Number” without decimal points so that the result is displayed as an even number.

14. In the first cell on row 2 in the “Days_Range” column enter the following formula:
=IF(AND(AS2>=0;AS2<=2);"a0-2";IF(AND(AS2>=3;AS2<=5);"b3-5";IF(AND(AS2>=6;AS2<=10);"c6-10";IF(AND(AS2>=11;AS2<=15);"d11-15";IF(AND(AS2>=16;AS2<=29);"e16-29";IF(AND(AS2>=30);"f30+";""))))))

15. Fill the last two cells on row 1 down to the last row of text to populate each row with the formulas.

In the formulas in steps 12 and 13 there are cell location references that I would like to have included in the macro instead and without cell-references so the macro will produce the result in the “Days_Out” and “Days_Range” columns.

See attached file for the above example table unchanged on the first sheet and changed on the second sheet. This is a big request but I hope someone is willing and has the time to help me out

Hi. Brilliant, thank you so much! There is a problem however. When running the macro it stops at the following line at the end and gives me the error message "Run-time error '1004': Application-defined or object-defined error":

.Offset(1, 0).Resize(Lr - 1).Formula = _
"=IF(OR(C2=""Closed"";C2=""Cancelled"";C2=""Resolved"");"""";TODAY()-" & rngSubmit_Date.Offset(1).Address(0, 0) & ")"

Any ideas?

Originally Posted by AlphaFrog

It worked for me with your example workbook. For my version of Excel, I did use the formula with commas from the commented line below, but I don't see how that would make a difference.

Did you get this error on the example workbook data or with other data?

Good input! I changed the semi-colons in the formulas to commas and then it worked fine and my Excel 2010 correctly changed them back to semi-colons in the cells. So the macro itself needed commas even though my Excel version require semi-colons. Thank you for your help mate!