Sarah has attended:
Excel Intermediate course
Excel Advanced course
Macros
In the training session we created some simple macros that used cell references. Is it possible to create macros that use
RE: Macros
Hi Sarah,
Thank you for the forum question.
I think something is missing in your question. Create macros that use???
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Macros
Yes sorry! I thought I would complete the feedback and then come back to my question later...
So when we were looking at macros, we created macros that used cell references, but I wondered if it is possible to create macros that use your column title as a reference rather than the actual cell reference?
The reason I ask is that I often export data from our departmental database to Excel. I can keep the column headings the same, but I might add in an extra column of data somewhere else in the file one time. So then perhaps the data that I would normally run the macro on has shifted one column to the right. Then perhaps if I wanted to run the same macro to help me clean up the data, it would run in the wrong column if it can only work using cell references?
Thank you for responding so quickly, and for running a great session yesterday.
Sarah
RE: Macros
Hi Sarah,
I am happy that the day was useful, and thank you for the nice feedback.
Unfortunate what you want cannot be recorded (the stupid computer:-)), but the code below will find the heading Monday and select the whole column.
You can then record what you want to do with the data and as we did on the course create a RunAll macro which first run the code below and then your recorded macro.
Sub FindRightColumn()
Cells.Find(What:="Monday").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
End Sub
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Macros
Thank you so much. I will have have a go with your code and see if I can make it work.
Best wishes,
Sarah
RE: Macros
Hi Sarah,
I was running out of time yesterday so I didn't tell you that you will need to save the workbook as a "Macro enable workbook", when you have macros in the workbook.
After you have created the macros go to the save as dialogbox and click on the down arrow where you have Save as Type and select macro enable workbook.
Good luck with the task.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
