Why am I Getting a Run-time Error '91' error after adding CurrentUser to code below?

This post is related to another post that was solved but created another issue, because of my lack of thinking ahead. here is the link to the original post in case it might help..... http://www.excelforum.com/excel-prog...ing-vba-2.html

The below code sorts data by date to find newly updated data and creates a new sheet and places the updated data into it, which works absolutely wonderfully.

Please Login or Register  to view this content.

Here is the part where I didn't think ahead. The sheet will be updated multiple times per day by multiple users, so the above code sends all the users updated data for the "current" day. I need it to send the updated data by the date and current user. In column B is where the current user ID/Name appears. I was provided with the code below, basically just adding the CurrentUser lines....

Please Login or Register  to view this content.

and now I get a "Run-time Error '91': Object variable or With block variable not set at the following line

Please Login or Register  to view this content.

I have tried different declarations for rngFind and current user but can't seem to get it figured out.
If anyone has any suggestions, I will try (just about) ANYTHING at this point.

Sorry for the long post.

Hello gmr4evr1,

The problem is the variable CurrentUser is neither defined or assigned a value. As per the "Solved" post, you need to declare this variable and then assign it a value.

You mentioned you have the a list of users names. I believe it was in column "B". Are these names the logon user names ? If so you can retrieve the name of the current user this way...

Please Login or Register  to view this content.

Howdy Leith,

Thanks for your suggestion. I have tried adding it to my existing code that is posted above and I am still getting the same error on the same line. So, most likely, I am not doing something right somewhere, but, I have no idea what or where.

Yes, the user names are logon user names and they are placed in column "B" with a separate function. Here is the code for the function that is in a separate module and does work...

Please Login or Register  to view this content.

Hello gmr4evr1,

You don't need a separate function. The code should be added to Macro1. The code below has the changes added.

Please Login or Register  to view this content.

Leith,

Thank you again for your help. The way you have the code written above is exactly the way I tried the first few times and was still getting the error. I even "deactivated" the function code just in case it was causing the issue, but it still didn't work. So this time I copied and pasted the code you provided with the changes, and guess what.....I still get the same error @ the same line

You will get the error you describe here if rngFind is Nothing.

Please Login or Register  to view this content.

That's because you are trying to find the Row of Nothing.

Try separating the If statement out.

Please Login or Register  to view this content.

Norie,

Thank you for your input, while that did take care of the original reason for this post, I am now faced with another issue....UGH!

Norie/Leith,

When it loops back through the code, it starts at the beginning and tries to create another sheet with the date. Of course it errors at that point because you cannot have two worksheets with the same name. How can I get it to start the loop after the headings part of the code?

What is the code meant to do?

Its meant to create a new sheet with the current days date as the name of the sheet, look in column "A" and find the "NOW" date, then look in column "B" and find current user (logon) name, then copy and paste the rows that meet that criteria to the newly created sheet. The only thing it does so far, is create the sheet and name it the current date, then loops back through and tries to create another sheet. It doesn't copy/paste any of the data.

Have you considered using a filter and copying the results to the new sheet rather than looping?

I have not, I don't know enough about VBA to go about doing this. The code I am using was provided to me by another forum member, you and Leith have helped me edit it to get to the point we are at now. The reason I am trying to do this is that the "master" sheet is added to daily, at least twice a day by different users. Once they have finished updating it, they click a button and it automatically sends an email, among other things. The problem was that the email contained everything in the master sheet, and I need it to send the updated portion only. Hence, the code I am trying to use now, it is supposed to create another sheet, with the updated info only, then email the newly created sheet. Everything, other than the users updating the info is to be automated, so there will be no actual user selecting anything for the email, the code will (well, is supposed to) do that for them.

Ok, while stepping through the code I noticed that when it gets to

Please Login or Register  to view this content.

the code skips to the second End IF and never looks at the rest of the code between

Please Login or Register  to view this content.

IS it finding "nothing" and that is why it is skipping, or, did I mess something up with the code?

Hello gmr4evr1,,

Yes, you correct. The object rngFind was set to the special object Nothing. If it had been successful then it would have returned a Range object.

I'm going to be honest, this code just isn't right.

I think we need to see a workbook with before/after data.

EOSR Sample For EF.xlsm
Norie,
Ok, here is an updated version of my before/after that was posted in the other thread. The "Before" tab is the "master" sheet, but it is also what is being emailed when the "save, close and email" button is clicked. The "After" tab is what I want it to email. The tab with the date on it is the one that the code I am having a problem with is creating, its not listing the updated information. The code in question is in Module 2. There is other code being used as well, but the module 2 code is where the issue is. It is supposed to create the new sheet based on the "Now" date in column A, AND the CurrentUser that is logged on in Column B.

Originally Posted by Leith Ross

Hello gmr4evr1,,

Yes, you correct. The object rngFind was set to the special object Nothing. If it had been successful then it would have returned a Range object.

Leith,

I understand what you are saying, what I don't understand is what the range is that it is "looking" for. I thought the range was A1 since it's looking at .Cells(1, 1). If this is correct, then it should find a date there....or am I just way off base with my thought process?

Any more info on, or luck with this?

Norie, Leith, Anyone have a way to do what I am trying to do? Or should I start another post?

Hello gmr4evr1,

I had to make a lot of changes to your macro to get to work. The macro as it is now is shown below and has been added to the attached workbook.

Please Login or Register  to view this content.

THANK YOU Leith!!!!!!! IT WORKS, it works, it works!!!! Man, I have been at this for far too long and you have given me a solution and I greatly appreciate it. Once I figure out how to give as much rep as I want, I will give you oodles and gobs of it!! Thank you again!!

Hello gmr4evr1,

You're welcome. Glad I could help out.

Leith,

Bad news.....The code is sorting by CurrentUser as I wanted, but the other "sort" I was looking for was for it to sort by the current date (TODAY) as well. So, it looks in column A for todays date, then column B for the current username and places only that data on the new sheet. When I ran it today, it put everything for the current user on the new sheet, including data from yesterdays date.

Hello Hello gmr4evr1,

Sorry about that. Just needs a small addition to make that happen. It is marked in blue.

Please Login or Register  to view this content.

Leith,
Brilliant! Thanks again....I think we (YOU) got it this time.