UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Macro Copy Vlookup To Column, Office 2013    
 
   
Sotolin
post Jan 24 2020, 03:56 PM
Post#1



Posts: 102
Joined: 27-August 03



Hi Everyone,

I'm not as familiar with macros so I'm having a hard time finding or even searching for the correct words in google.

Here is what my situation is.

I have a list of employees and their assignments that gets updated every week. So with that being said the size of this list could vary, one week it could be 100 and the next week could be 110 or 95 just to put numbers on it.

What I need is to be able to create a vlookup based on their assignment ID to bring a description of the job they are doing.

Yes I can just create the formula and copy paste but we are trying to automate everything without human interaction so I need to be able to tell excel to apply that formula to the list.

Is there a way to do this and tell the macro to look at a certain range based on what we get every week? or maybe tell the formula to stop at the end of the information?

Again this may be simple for most people, I'm just not familiar with Macros.

Thanks for the help!!!
Go to the top of the page
 
June7
post Jan 24 2020, 04:08 PM
Post#2



Posts: 1,313
Joined: 25-January 16
From: The Great Land


I am sure is possible, most anything is with enough code. Attach a sample workbook to post.

Why not use Access?


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Sotolin
post Jan 24 2020, 04:34 PM
Post#3



Posts: 102
Joined: 27-August 03



Unfortunately I can't user access as it is not available where I work.

Attached is a sample file.

I will need Column F to be populated with the vlookup.

The vlookup table is on D17:E27. This lookup table will not be updated on a weekly basis so it is pretty static.

What changes is the list of assignments... In this sample we 10 assignments as 1 employee has 2 which is really not relevant I guess.

The idea is to create the macro to do a vlookup on F4 and copy down to whatever the lists ends, whether is row 13 or 100.





Attached File(s)
Attached File  sample_for_vlookup_macro.zip ( 13.63K )Number of downloads: 1
 
Go to the top of the page
 
June7
post Jan 24 2020, 05:52 PM
Post#4



Posts: 1,313
Joined: 25-January 16
From: The Great Land


A workbook with VBA procedure must be saved as an xlsm file.

Consider this code placed in a general module which acts on whatever sheet has focus:
CODE
Sub FillFormula()
    Range("F4").Formula = "=VLOOKUP(D4,$D$18:$E$27,2)"
    Range("F4").AutoFill Destination:=Range("F4:F" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

Now the real trick is figuring out how to execute this. How is worksheet updated? Are you provided a new workbook periodically? If this code has to be added to a new workbook, why bother with VBA? Just use worksheet autofill by double clicking lower right corner of cell, not copy/paste.

This post has been edited by June7: Jan 24 2020, 05:53 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th April 2020 - 05:20 PM