Creating a macro with variables
Hi all, I currently have a macro that updates/saves a number of different data sets for a specific period. I need to be able to make it be able to update the data sets for various different periods.. how would I go about doing this? Appreciate any help whatsoever! Thank you in advance for any tips!
Please take the time to read the forumrules, especialy the one's about crossposting
http://www.mrexcel.com/forum/excel-q...variables.html
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Thank you, I've looked through but I'm very new to macro and not exactly sure how id go about doing this. My current macro is below. It updates data for different variables for the period 2014 quarter 4. I want it to be able to be flexible so for example I can type in 2015 q3 and test 5 (different folder to save this data in) and it will change all areas where it says 2014 q1 and the word test. Do you suggest creating a macro to do this or a master list.. And how would I go about doing this? thanks!!
Please Login or Register to view this content.
Hi,
Create and name some variable cells on a sheet that will hold your values 2014, q4, test etc. Name them say 'year', 'qtr', 'text'
Then in your macro since you'll use them several time Dim some variables to hold them.
e.g.
Now in your code use these variables. e.g.Please Login or Register to view this content.
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
That definitely looks like the type of function I'm after.
Can the sheet with the variable cells be on a new workbook? And how do I link this sheet with the macro?
I have an error coming up in the lineI have a feeling its because I haven't saved the sheet with variable cells correctly..Please Login or Register to view this content.
Hi,
Yes, the variable cells may be on a completely different workbook. Obviously when you read the variable cells, instead of just using the range name (which by default is assumed to be in the workbook containing the macro you're running) you'll need to fully qualify this. e.g.
Did you Dim the variable as lYear as per my suggestion or just 'Year'? If so you need to usePlease Login or Register to view this content.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks