Functional VBA code errors out when included in Worksheet.Change event

First time posting a question – before that, I would like to say thank you to this group for their past work – I’ve found several solutions / guidance that led me to solutions over the last year.

Here the problem that has me stumped. I have a Worksheet.Change event that triggers two worksheets to be sorted via a Do until loop which works fine. I want to add code to copy a formula to a column range – basically reset those cells where I have overwritten the formula through manual input. The code works fine when it’s included in a standard module, but when the same code is included in a Worksheet.Change event, the first pass though the loop works fine (on Sheet1), but on the second pass (Sheet2) I get a Run-time error: 1004 - Select method of Range class failed on this line of code:

Range("N4", Range(endcell).Offset(0, 13)).Select

Here's the full code:

Please Login or Register  to view this content.

I’m mystified how this code can work fine in a normal module, and even work once in a Worksheet. Change event, but error-out on the second pass. Hopefully this is something obvious related to the differences between the two, but I’m stumped. I’ve searched on the internet, but have had no luck. The attached workbook was created to test whether there was some corruption in the original workbook causing the problem, but it behaved the same way. If you first run the macro using the button, the formula in Sheet1, A1 will be copied to the yellow highlighted cells. If you then reopen the workbook (just to reset / clear everything), then change the value in Sheet1 A29 you’ll see that the yellow highlight is completed on Sheet1 and then you will get the runtime error. . Appreciate any suggestions. Aquxiano

Thank you for your reply.
- The formula is exactly the same on both sheets, I could duplicate it on sheet2, but since its exactly the same, I didn't figure I needed to - not sure I see the advantage unless that's the reason the code fails.
- Regarding setting the variable to sheet1, it is redundant on the first pass, but necessary for the second pass because the variable becomes sheet2 (if there's a better way to change the variable for the second pass - I don't know what it is
- What is preferable to selecting sheets/cells?
Since I didn't explain it clearly the first time, let me take another stab at it. What I'm trying to do is create a worksheet.change event that triggers when the date on one worksheet is changed and will copy a formula to a range on two different sheets
- I excerpted the problem code because I though it would just muddy the water to include the original workbook with a lot of other code - which also includes the code that doesn't work. Would that type of detail be helpful?

True, but I didn’t figure that one formula vs. another would be the cause. My assumption, my mistake. Attached is my file. Note that Sheets “Do Anything Very Easy” and “DAVE-Bear Flags” are both first sorted by Column K, then by Column G. The hyperlink in Column G does this sort. On the “Do Anything Very Easy” sheet, when you change the date in cell R1, both sheets are resorted alphabetically. Before you do this, note the closing price on line 5 is $9,999.00. This isn’t a real price, but an example of manually overwriting the existing Index/Match formula. I’ve left the problem code live so when you change the date you will get the error previously described. The error occurs while trying to update the bear flag page (note that TXP now shows the closing price of $78.53 as a result of the formula being copied over the manual entry.
I’ve highlighted the offending code between two rows of asterisks in the Worksheet.Change code toward the end. If you want to comment this out, you can switch between sorting by Columns A, G, or J by clicking on the respective column headings/hyperlinks. Sorry for the confusion, I was trying to make the question simple, but like I said this is my first question, so I learned something new.

Thanks for your effort and the sample code. Yes, there is a lot of code in the real workbook – primarily navigational – jumps me to the next cell requiring info past those cells with formula or changing cell colors to highlight different conditions I want brought to my attention. Your code:

Range("N4", Range(endcell).Offset(, 13)).Formula = Range("A1").Formula

Is a slick way of “copying” a formula – I didn’t realize you could do that – I’m assuming this will retain the relative/absolute nature of the source formula correct? In the past when “resetting” a formula I’ve set the target formula using this code:

ActiveCell.Offset(3, 3).Value = "=ABS(Target-I42)*100"

But in the current instance I needed the formulas to be relative I didn’t know if that was possible when in essence I was hard coding the formula into VBA. Thanks for the tip.
You’ve got a couple commands I’m not familiar with so I need to research to understand how your code works to see if I can incorporate it. Thanks again for the help.

@TMS – I did look at your sample workbook briefly last night, but it didn’t work the first try and I had to finish some other stuff before today so I tabled it. Tried it this morning and it worked fine – I bet that my EnableEvents had been turned off during a failed VBA run and I didn’t check to turn it back on. I see now the VBA generates relative addresses. I’ll spend more time with your code today.

@TMS/mikerickson – “OP code?” – Operator Code?
I thought fully qualifying reference meant avoiding things like “Activesheet” or “Thisworkbook” using actual references for everything. Pardon my ignorance, but I don’t understand how that is an alternative for selection. Can you give me a 5cent example or refer me to some where I can read up. I did search the internet trying to learn this myself, but am not confident I found the right context for your point.

@ mikerickson – I_Mformula is defined at the workbook level. I did it that way so that I could copy it to both worksheets. (Honestly I’m not sure if that make a difference, but in my past experience I’ve found it better to define at a workbook level.) Originally when I started using a lot of range names I thought it make more sense to define at a worksheet level generally, but subsequently had to redefine them at a workbook level as my code evolved. That’s only one instance, but it was a pain to redefine 50-60 range names to a workbook level so, now I generally default to the workbook level. Do you have a default preference for defining range names?

Thanks for your input,
aquixano

"OP" = Original Post. ie, your first post.

@BellyGas/TMS - Thanks.

Thanks TMS - always good to improve skills. I know that in a dynamic situation I can use the offset property from a named range to change another cell - I'm thinking of reasons I've used Select in the past. I know I've had situations in the past when a piece of code wouldn't work until I Selected the range -don't remember a specific example to cite, but from what's been said already there must be a different resolution rather than using Select property. I'll try and avoid using Select in the future and post a question the next time I can't resolve an issue without using Select. Is the "Activate" property also to be avoided like Select?