Avoid Copying Macro

0

I am attempting to create a macro that would impact only certain fields in certain columns without impacting the other fields in a spreadsheet.  I have been told that for each cell a macro would need to be created, but then it could be copied to each of the other impacted cells to get my desired end result.  But, surely there is a better way.  I have rows & rows/columns & columns of data, to copy it over and over seems silly.  There has got to be a better way I feel.  These would be basic If/Then type macros.  Inserting text or deleting text from certain cells based on whether or not a rule is met or not, is the deal.  Thanks!

Answer
Discuss

Answers

0

First off, yes, of coure there is a better way. The problem is rather that there are several and advice can't be given without knowing much more precise detail of what you wish to achieve. Please look at "event procedures".

As the name implies, an event procedure is a macro which runs when a specific event occurs. There are many such events, such as when a workbook is opened or closed, when a worksheet is activated, deactivated or modified, when a cell is clicked or modified and more. From this short and incomplete list you can see that there are application events, workbook events and worksheet events most of which are, in fact, cell events. For your question, a worksheet event is the most likely candidate to provide a solution.

Event procedures have a fixed format. You can write them but it's easier to let VBA do the setup. To do so you need to activate the code sheet of any worksheet in the VB Editor. At the top left you will have a dropdown offering the choice between Worksheet and (General). If you select the former the dropdown on the right will be filled with the names of available events. Selecting any of them will create an empty event procedure for that event. You will probably want the Change event.

When you try it out you will see that the procedure has an argument called Target (As Range). Run a simple test. Add this line of code to the procedure.

MsgBox "Cell " & Target.Address & " was modified."

Now go back to the worksheet and modify any cell. As soon as you confirm the change the message box will pop up telling you which cell was modified.

Now change your code as follows.

If Target.Address = "$A$1" Then
    MsgBox "Cell " & Target.Address & " was modified."
End If

The effect is that the message box will show only if cell A1 was modified.

I suspect that the solution to your problem can be found in the capabilities contained in this example. You can run specific code in response to changes made to specific cells. It could be the same code for a range of cells or different code depending upon which call was changed. The Selection_Change event works similarly but is triggered by a click only, not requiring a change.

Discuss

Discussion

Thank you, sir for your answer.  Please let me elaborate so that we are sure to be on the same page. I am an appraiser.  I am looking to make my work as efficient as it possibly can be.  The more efficient, yet still accurate and reliable, the more money my firm can make.  In pursuit of this objective, I employed a local computer retail shop and they agreed to design what I had explained was my objective.  Through the use of Access, I am now merging sales data from two different sources into one csv file.  Initially, the merge was quite large as I merged a full year of data for my local market area.  Now, I simply merge by the month so the file isn't as large as it once was initially.  There are less than, or about, 150 records now each month.  
statonandassociates (rep: 2) Nov 9, '18 at 10:44 am
Continued...

Anyway, the output that is in csv format is then uploaded into my appraisal software and saved to my software database, which is built into the software.  Currently, each record is 90% complete.  I am still saving to my database although it isn't perfect.  I want it perfect.  I inquired about the few issues still remaining to be corrected and was told the only way to do that is to create macros to the csv file prior to the upload into the software database.  So, this is the problem which resulted in me researching and finding this website and hopefully this will result in the final piece of the puzzle to make my project complete.  I can send you a merged file if you want to learn more about it or move forward in this discussion.  If this is something that needs to be referred to someone else who might have more time to address, please refer me.  Or, if you want to tackle it, that's great.  I don't want to be a free-loader, so if this is a project that is more time consuming that is appropriate for this forum, please let me know and we can discuss compensation for your time or someone elses.  Thank you so much!  I look forward to hearing back.
statonandassociates (rep: 2) Nov 9, '18 at 10:45 am
I agree that your request doesn't fit this forum. I have set up a dedicated email account: variatus@mail.com. Let's take our discussion there. Please start by sending me a copy of your CSV file and the layout you want for your reports.
Variatus (rep: 2025) Nov 9, '18 at 12:19 pm
I am just now seeing this response.  I apologize for the delay, but I am thrilled you will listen to my issues.  Thanks and I will send a file soon.
statonandassociates (rep: 2) Nov 13, '18 at 5:25 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login