addition to macro - automatically populate column with email address

Hi

I would like to add to the macro assigned to the "import" button on the attached sheet so if will do the following...

1. Once the information from the import file has been copied and pasted into the query log the supplier name in column E will look for the appropriate email address from the "supplier email address's" worksheet and return the corresponding email addre  ss from column B into column W.

Note the list of supplier email addresses list will get longer so a looping function will probably need to be added.
Master-Query-Log.xlsm
mikes6058Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mikes6058Author Commented:
Note: At the moment the supplier email addresses worksheet is just an example.

Once fully complete it will have a list of 200+ supplier names with a different email address for each
0
Roy CoxGroup Finance ManagerCommented:
Why not just use a VLOOKUP formula?
0
Saurabh Singh TeotiaCommented:
You don't have to loop..You can use a simple macro like this which will do what you are  looking for...

Sub populateemails()
    Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "e").End(xlUp).Row

    If lrow > 1 Then

        Range("F2:F" & lrow).Formula = "=IFERROR(VLOOKUP(E2,'supplier email addresses'!A:B,2,0),"""")"
        Range("F2:F" & lrow).Value = Range("F2:F" & lrow).Value
    End If

End Sub

Open in new window


Saurabh...
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Roy CoxGroup Finance ManagerCommented:
You don't even need to use VBA to add the formula. Mike I suggest that I tidy up the actual workbook so that we can use more inbuilt Excel features without resorting to VBA for everything.

I have to go out now so I'll check your reply when I get home.
0
mikes6058Author Commented:
I know I could use vlookups the reason I wanted to use VBA was simply so the vlookup formula automatically copies down when a new row is pasted using the current import macro.

However a tidy up of the workbook does sound like a good idea.

thanks
0
Roy CoxGroup Finance ManagerCommented:
As I said, if you let me change tidy up the actual workbook the formula will copy down. In fact even without this the formula should copy down down. I'll be home after lunch and will set up the workbook and post examples for you.
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I've tidied up the workbook and converted the data to Tables. These have many benefits and will make coding simpler.

Take a look at you will see I've removed the buttons and added a new Tab called Query Tab on the left of the Ribbon to replace them.

I've added the formula to add the emailaddress from the list. You will see that by adding data this formula will copy down - no need for VBA for this part.

Let me know what you think.
Master-Query-Log--3-.xlsm
0
mikes6058Author Commented:
Hi Roy,

Apologies its taken me a couple of days to respond. Thank-you for tidying the workbook up. I particularly like what you have done with  the buttons.

First initial observations.

1. When a new query is added to the log it is not adding it as the bottom row and is leaving a space. This also causes the UID number to go back to 1. I think this is caused by the fact the vlookup formula has been copied down to one cell beneath the bottom row. However I tried deleting the formula from the cell and re importing but got the same result.

2. There is no macro assigned to the send update button.  I can't seem to figure out how to do it. Could you provide me with the appropriate steps.

Thanks
0
Roy CoxGroup Finance ManagerCommented:
I'll check this evening. I will also need to change some of the code to accommodate the changes. I was waiting to see if you liked the new set up.
0
mikes6058Author Commented:
Sounds good, ah I see, sorry for jumping the gun!
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I've re-written the import code. It will now work with both query types providing the random query workbook name always starts with random. So now you only need one procedure to run for importing.

I haven't checked the other code. It now working with the Ribbon button but needs amending which I'll look at later

The formulas copy down as the query is imported.
Master-Query-Log--4-.xlsm
0
mikes6058Author Commented:
Hi Roy,

Thanks, a few comments.

1. The macro is not removing the visual source formatting - This means cells colours and fonts etc. are being copied over to the query log. More importantly columns J L M are returning #NA instead of the correct values.

2. I have added two entries to the attached sheet. You will see that A17 has the value Q335 when it should be Q334?

3. Could you change the entry rule so that rather than the random query workbook name having to start with "random" it must start with "member" instead.

If you could address these two issues that would be great.

Thanks
Master-Query-Log--4-.xlsm
0
Roy CoxGroup Finance ManagerCommented:
I see what is happening. I'll get back to you soon
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I hadn't noticed formulas in the member query file, hence #N/A

I have changed the code for removing the formats and I think it is OK now. Please check and let me know then I'll have a look at the macro that sends the list
Master-Query-Log--4-.xlsm
0
mikes6058Author Commented:
Hi Roy,

Points 1 and 3 from my previous post have been resolved however point 2 is not.

Q335      21/04/2015      334

In this instance Q335 should be Q334.
0
mikes6058Author Commented:
With regards to the Send update macro.

The macro assigned to the button "send supplier update" on the attached sheet works correctly and needs to be assigned to the "send update" tab button on your workbook.

Thanks alot
Mike
Master-Query-Log-V02--1-.xlsm
0
Roy CoxGroup Finance ManagerCommented:
Sorry Mike. I fixed that part but then reverted to a previous version and forgot to change back again.

The Send Suppliers is assigned to the button but I cannot test it at the moment because I don't have outlook on this computer.
Master-Query-Log--4-.xlsm
0
mikes6058Author Commented:
That's great. Everything works on the "send update" macro apart from the final stage where the rows that have been emailed should also have had column Z populated with the date the update is sent (e.g if it was sent today, todays date would be displayed).

Obviously a date would not appear in the excluded rows (e.g. EXTERNAL or RESOLVED)

Mike
0
Roy CoxGroup Finance ManagerCommented:
Is that the code for sending the list? I didn't write that code for you but I will have a look at it for you later. I'm sure that it was that code that I suggested using a Filter with and as data is added performance will be affected by using Loops. I'll try to take a look this evening.

Edit: just had a quick look and I think this will fix the date issue;. I still think that a filter would be far more efficient especially as the data increases.
Master-Query-Log--4-.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mikes6058Author Commented:
That's spot on. I think your'e right about the filtering method. I'll test the current macro with a larger data set and if I find it's becoming laggy I'll upload a new question notifying you at the time.

Thanks again
Mike
0
mikes6058Author Commented:
oh and just a quick one. How do I find the macro (i.e syntax) for the second macro?

When I click on the developer>>macro's only the "EmailIt" macro appears???

I need to change the text in the body of the email that is sent....

Mike
0
Roy CoxGroup Finance ManagerCommented:
That's the one that I wrote and it's called from the import macro. The other's aren't appearing in the list because they are called by the Ribbon and are in effect Private.
0
mikes6058Author Commented:
So how would I edit the VBA on the other?
Sorry if I'm asking a stupid question?
0
Roy CoxGroup Finance ManagerCommented:
Sorry which one requires editing?

Developer Tab -> Visual Basic, then open Module 1. Make a backup first.

By the way there are no stupid questions, everyone needs to learn
0
mikes6058Author Commented:
I need to edit the macro assigned to the "send update" button.

Module 1 would give me the code for the Import query button.

Mike
0
Roy CoxGroup Finance ManagerCommented:
There are three procedures in Module 1,  You need Sub EmailIt()
.

You can edit the others but do not remove the Callbacks.
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

I missed your comments about filtering. If you let me have an example workbook with more data in then there's a timer procedure we can use to check the difference. I have made these comments in many posts on Excel Forums and always had positive comments back.
0
Roy CoxGroup Finance ManagerCommented:
Hi Mike

Here's the updated version. It runs using Advanced Filter so will be much faster than looping, especially on larger datasets.

I'm having problems uploading with Google Chrome. Here's alonk to download it from

Updated File
0
mikes6058Author Commented:
Hi Roy,

I haven't been able to test the macro as I am getting a compile error. I have opened a new question as I have also added some additional queries/requirements.

See link Below

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28663202.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.