Range issues I think? New to Macros

Please Login or Register  to view this content.

Hi. This is the first real Macro that I have created. It seems to be working great except for when I try to update a user that Already exists in my spreadsheet. Basically here is how the Macro's work together(One from Excel and one from outlook). I have a rule set up in my outlook that will move emails with a certain title to one of my folders and then run a macro on that email. It goes through the email and opens one of my excel dosuments and inputs some data into. It then saves the excel file which activates the excel macro.

In the excel workbook I have two spreadsheets. On contains usernames in column A and empty cells for the other three columns that I want to update as these emails come in. The second spreadsheet contains the info from the outlook Macro. The Macro Works good except for when I try to update the three empty columns for an existing user. Both Macros are below:

Outlook Macro

Please Login or Register  to view this content.

Excel Macro(the part that updates a current user on spreadsheet is highlighted)

Please Login or Register  to view this content.

Let me know if you have any questions.

This is Outlook and Excel 2010 by the way.

Hi

Can you set up an example file that is in a state just before the beforesave macro is run so we have something concrete to review.

rylo

This is how the spreadsheet should look after it is updated by the outlook Macro. I didn't put the macro in the spreadsheet in the example.

Hi

I opened your example file from #3, created a general module, put in your beforesave macro, renamed it to be aaa (and removed the public) and stepped through it. it put data into all 3 cells in Sync Date B21:D21.

One thing I did notice is that you go to a lot of trouble to change Username20 to username20, put them into separate variables (User and Lookup), but then use User not Lookup when you are setting the find range variable rng. Is this right?

Try doing what I have done with the general module and see if things work out OK. If that works, then manually step through the workbook_beforesave macro and see if it works when run manually.

If the general module works and the workbook one doesn't then try having the workbook run the general one and see if that works.

But at this stage, to me the code seems to be working.

rylo

The lookup variable is designed to rturn either a username or a 0. They macro will do differnt things based on this. If it is a zero it will add a new user to the bottom of the list on the first tab. My problem is that whenever a current user is found it always adds the info in the three columns for the first user on my list. This is when the macro runs after XLwb.save is called out in the outlook macro. This activates the excel macro. The weird thing is that I don't have this issue when i run the Macro in excel, only when the macro activates after outlook calls the XLwb.save. Is there a better way to do this?