Using Vlookup in VBA to Find Associated Value, Copy, and Paste

[jhar123456]

Hello,

I am new to macros and am having a bit of trouble attempting to setup my code thus far. I am trying to do the following:

I have a spreadsheet in Excel with multiple sheets. On sheet "Imp. and Def. Rank Template" I have essentially a form setup where I fill in various cells of information when creating a new entry. Once complete, I click my existing macro (which is working correctly) and among other things, it takes all of the relevant data from my entry and populates it in my database sheet titled "Open Imp. and Def." on the next available line. In the event that I would like to update an existing entry (i.e., an entry that was previously created in "Imp. and Def. Rank Template" and then stored in "Open Imp. and Def.") I would like to have a macro which will recall the relevant information from "Open Imp. and Def." and insert it in the appropriate places back into "Imp. and Def. Rank Template".

Essentially, I'd like to go into "Imp. and Def. Rank Template", type my reference number into "B2" of the entry that I'd like to revise, and run a macro which will look for the value currently in "B2" in "Open Imp. and Def." in column "C". If it finds it, it will copy the value in the 3rd column of that row (column "F"), and then paste that value back into "Imp. and Def. Rank Template" in cell "C3".

Once I get the code for that line, I'd like to copy it to repeat the process for different values (i.e., 4th column value to be pasted in cell B5, etc.). Thank you for any help!

 

[ajamess]

I would do something like:

Sub Check()
dim x as long
dim template as worksheet
dim database as worksheet

set template = activeworkbook.sheets("Imp. and Def. Rank Template")
set database = activeworkbook.sheets("Open Imp. and Def.")
x = 0


Onerror Resume Next

x = application.worksheetfunction.match(template.cells(2,2).value, database.range("c:c"),0)

if x <> 0 then
template.cells(3,3).value = database.cells(x,6).value
End if


End Sub


That does your first example. Anything else you'd like to bring over just copy the line in the if, and change the targetlocation and column its pulling from the database.

 

[jhar123456]

This works absolutely perfectly. Thank you so much.

 

[ajamess]

Glad to hear it! Best of luck to you!