Frequently Used Macro now Giving a" Runtime Error '1004 Application-Object Defined Error"

Hi...again, lol

I have a macro that I've used with no problem countless times now, that for some reason is now giving me a "Runtime error '1004": Application-defined or object-defined error"

Basically, the macro is a way to help us determine email formats at my company.

(DISCLAIMER: We DO NOT spam. Everyone we send to have opted-in, however for privacy reasons, I couldn't include the actual email addresses, so instead, I put together an example from some other files and sources of my own.)

Now, with that outta the way...

Basically, the macro does the following:

1) It takes the prefixes in Column A that belong to a specific domain: (the domain is in Column D.)
2) In Column B, the macro does a simple "if" formula to check if the value in the previous row of Column D is the same as the value in the current row of Col D.
3) If the value is different, the macro assumes that the email addresses belonging to a particular domain are complete.
4) The macro begins a series of calculations in Columns E, F, G, H and I.
5) Everything is copied and pasted over itself as values only.

Now, for some reason at Line 53, the Runtime Error triggers. (This is not happened any other time previously.) Here is the line of code:

Range("E" & EndRow + 1).FormulaR1C1 = "=COUNTIF(R[-" & EndRow - StartRow + 1 & "]C[-4]:R[-1]C[-4],""*.*"")/COUNTA(R[-" & EndRow - StartRow + 1 & "]C[-4]:R[-1]C[-4])"

(At first, I thought I was missing a "." somewhere, but I don't think so...)

-I have not changed the macro from when I originally used it (mehmet, a user here was kind enough to develop it for me. it worked seamlessly!)
-I've checked and there are no #N/A or #VALUE or #DIV/0! errors in the data.
-I've attached the file for review.
-I've been trying to figure this out ALL day with no luck.

Any help would be greatly appreciated. (again).

Thanks,

R.J.

MACRO_TEST_w_SCOTTIEX_FIX.xlsm

Originally Posted by scottiex

For me it seemed to fail trying to run the on row below the last row where endrow gets set to 1.

The most simple solution is probably after you set endrow

Please Login or Register  to view this content.

but your description above implies your result was maybe different from mine?

also I note your formula seems to have you writing over the title of column B when you go

Please Login or Register  to view this content.

maybe you don't want to start that at B1 and instead start at B2? or you want to add the titles in later (are they new? could something related to that be what changed in the data when it started to fail?)

Anyway - that seems to let it run as well for me. Have a check and see what keeps the results correct.

Scottiex, man...you've been my MVP this weekend!

Ok, that helped a LOT.

For some reason, in my spreadsheet the macro keeps doing calculations in rows that have no data in them.

I've attached the spreadsheet with your fix included AFTER running it.

Any idea why it's doing this?

(take a look at rows 30-38 for a good example of what's happening)

I might have to see if I can get you on the payroll, Scottiex, lol.

Well, I'm sure even if yours is slower than Mehmet's original (which definitely was FAST) it would still be much faster than what I was doing before on my own.

Better check as I'm not 100% sure what your trying to do

I did run with yours but it's giving me an "Ambiguous Name Detected: Optimise" Compile error.

I'm familiar with that error, and I'm looking at the code you provided, and I don't see what it's having trouble with.

R.J.

If only I could work only doing excel programming I'd be a happy fellow

OK, I amended the code slightly - mainly to get the capitalization thing working - it was splitting the emails by the case they were written in.

It normally means you have two or more Subs with the same name in your project. Your code calls a Sub "Optimise" , but there are probably more than one Sub named "Optimise".

@Scottiex...

I removed the "optimise" and "deoptimise" and the code works perfectly!

And it's still plenty d-mn fast.

Originally Posted by JOHN H. DAVIS

It normally means you have two or more Subs with the same name in your project. Your code calls a Sub "Optimise" , but there are probably more than one Sub named "Optimise".

@John

That's what I thought. I looked through the code and didn't see anything other than the Optimise and Deoptimise