Creating a macro with functions and formulas on worksheet

timemachine

New Member
Joined
Jul 1, 2012
Messages
3
Hi,

I have inherited a macro which needs updating, from a functionality and logic point of view it is very straight forward but I am not sure how the macro was originally created.
It is a (hidden) worksheet, labelled Macro1, with each line having a either a function or formula and the the macro goes line by line and executes whatever function or formula is on that line.
So when you add a text file this macro will run through each function and parse and format the text file (which is in a csv format).

I have not been able to find anything on the web that even remotely looks to have implemented a macro like this, I know it works but how? and how do go about creating it.
I would normally think of just writing it in vba or recording a macro and have never seen it done this way.
So if anyone can explain how you go about creating a macro this way and any benefits.

Also some of functions do not look like legitimate excel functions ( eg Select()) to me so I don't know how it has worked so far.

If anyone can shed any like on this.

below is a snippet.

Code:
=SELECT("C1")
=TEXT.TO.COLUMNS(R1C1,1,1,FALSE,FALSE,TRUE,FALSE,FALSE,FALSE,,{1,1;2,1;3,1;4,1;5,1;6,1;7,1;8,1;9,1;10,1;11,1;12,1;13,1;14,1;15,1;16,1;17,1;18,1;19,1;20,1;21,1;22,1;23,1;24,1;25,1;26,1;27,1;28,1;29,1;30,1;31,1;32,1;33,1;34,1;35,1;36,1;37,1;38,1;39,1;40,1;41,1;42,1;43,1;44,1;45,1;46,1;47,1;48,1;49,1;50,1;51,1;52,1;53,1;54,1;55,1;56,1;57,1;58,1;59,1;60,1;61,1;62,1;63,1;64,1;65,1;66,1;67,1;68,1;69,1;70,1;71,1;72,1;73,1;74,1;75,1;76,1;77,1;78,1;79,1;80,1;81,1;82,1;83,1;84,1;85,1;86,1;87,1;88,1;89,1;90,1;91,1;92,1;93,1;94,1;95,1;96,1;97,1;98,1;99,1;100,1;101,1;102,1;103,1;104,1;105,1;106,1;107,1;108,1;109,1;110,1;100,1;111,1;112,1;113,1;114,1;115,1;116,1;117,1;118,1;119,1;120,1})
=SELECT.LAST.CELL()
mLastRow=ROW(SELECTION())
=SELECT("R1C1")
m1stRow=ROW(SELECTION())
=SELECT("C1")
=EDIT.DELETE(1)
=SELECT("C2:C10")
=EDIT.DELETE(1)
=SELECT("C3:C15")
=EDIT.DELETE(1)
=SELECT("C5")
=EDIT.DELETE(1)
=SELECT("C6:C7")
=EDIT.DELETE(1)
=SELECT("C7:C21")
=EDIT.DELETE(1)
=SELECT("C8:C32")
=EDIT.DELETE(1)
=SELECT("C9:C100")
=EDIT.DELETE(1)
=SELECT("C2")
=FORMULA.REPLACE("Some text to replace","With this bit of text",1,2,FALSE)
=SELECT("C1")
=INSERT(4)
=SELECT("C4")
=CUT()
=SELECT("C1")
=PASTE()
=SELECT("C4")
=EDIT.DELETE(1)
=SELECT("C3")
=INSERT(4)
=SELECT("C7")
=CUT()
=SELECT("C3")
=PASTE()
=SELECT("C7")
=EDIT.DELETE(1)
=SELECT("C7")
=CUT()
=SELECT("C9")
=PASTE()
=SELECT("C4")
=CUT()
=SELECT("C7")
=PASTE()
=SELECT("C4")
=EDIT.DELETE(1)
=SELECT("C2")
=FORMAT.NUMBER("0000000000")
=SELECT("C4")
FORMAT.NUMBER("000000000000000")
=SELECT("C7")
=FORMAT.NUMBER("0000")
=SELECT("R1:R"&TEXT(mLastRow,0))
=COLUMN.WIDTH(,"R1:R"&TEXT(mLastRow,0),,3)
=ALIGNMENT(2,FALSE,3,0,FALSE)
=SORT(1,"R1C1",1,,,,,0,1,FALSE)
=SELECT("R1")
=INSERT(2)
mLastRow=mLastRow+1
=SELECT("R1C1")
=FORMULA("Date")
=SELECT("RC[1]")
=FORMULA("A Number")
=SELECT("RC[1]")
=FORMULA("Origin")
=SELECT("RC[1]")
=FORMULA("B Number")
=SELECT("RC[1]")
=FORMULA("Destination")
=SELECT("RC[1]")
=FORMULA("Type of Service")
=SELECT("RC[1]")
=FORMULA("Duration")
=SELECT("RC[1]")
=FORMULA("Tariff")
=SELECT("C2")
=INSERT(1)
=INSERT(1)
=SELECT("R1C2")
=FORMULA("Date")
=SELECT("R1C3")
=FORMULA("Time")
=SELECT("R2C2")
=FORMULA("=MID(RC[-1],7,2)&""/""&MID(RC[-1],5,2)&""/""&MID(RC[-1],1,4)")
=SELECT("R2C3")
=FORMULA("=MID(RC[-2],9,2)&"":""&MID(RC[-2],11,2)&"":""&MID(RC[-2],13,2)")
=SELECT("R2C2:R"&TEXT(mLastRow,0)&"C2")
=FILL.DOWN()
=SELECT("R2C3:R"&TEXT(mLastRow,0)&"C3")
=FILL.DOWN()
=SELECT("R2C2:R"&TEXT(mLastRow,0)&"C5","R"&TEXT(mLastRow,0)&"C3")
=COPY()
=PASTE.SPECIAL(3,1,FALSE,FALSE)
=SELECT("C1")
=EDIT.DELETE(1)
=COLUMN.WIDTH(,"R1:R"&TEXT(mLastRow,0),,3)
=SET.PRINT.TITLES("R1","")
=SET.PRINT.AREA("")
=PAGE.SETUP("&A","Page &P",0.75,0.75,1,1,FALSE,FALSE,FALSE,FALSE,2,,90,1,1,FALSE)
=SELECT("R2C5")
=FOR("counter",1,mLastRow,1)
mCell_Content=GET.FORMULA(ACTIVE.CELL())
=IF(LEN(mCell_Content)=9,FORMAT.NUMBER("0#########"))
=IF(LEN(mCell_Content)>10,FORMAT.NUMBER("00####################"))
=SELECT("R[1]C")
=NEXT()
=RETURN()
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Looks like it might be an antique from the days of Macro Sheets before VBA as it is today. It would probably be easier to just write a new macro for what you need.
 
Upvote 0

Forum statistics

Threads
1,211,457
Messages
6,101,961
Members
447,766
Latest member
cool_aikon

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