vlookup as a macro

%3CLINGO-SUB%20id%3D%22lingo-sub-206811%22%20slang%3D%22en-US%22%3Evlookup%20as%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206811%22%20slang%3D%22en-US%22%3E%3CP%3EI%20wish%20to%20use%20a%20lookup%20using%20a%20macro%20rather%20than%20a%20formula%20the%20data%20I%20wish%20to%20look%20up%20is%20contained%20within%20another%20workbook%20named%20%E2%80%9COps%20centre%E2%80%9D%20which%20is%20not%20open%20the%20data%20is%20on%20a%20sheet%20named%20%E2%80%9Cparts%E2%80%9D%20in%20this%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20open%20work%20book%20performing%20the%20look%20up%20is%20called%20%E2%80%9Cestimate%20universal%20mod%E2%80%9D%20when%20a%20value%20is%20entered%20into%20any%20of%20the%20following%20cells%20%26nbsp%3B%E2%80%9CB49%2C%20B50%2C%20B51%2C%20B52%2C%20B53%2C%20B54%2C%20B55%2C%20B56%2C%20B57%2C%20B58%2C%20B59%2CG49%2CG50%2CG51%2CG52%2CG53%2CG54%E2%80%9D%20which%20I%20would%20make%20active%20so%20macro%20runs%20on%20data%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20the%20look%20up%20to%20check%20the%20value%20entered%20against%20the%20closed%20workbook%20%E2%80%9COps%20centre%E2%80%9D%20sheet%20%E2%80%9CParts%E2%80%9D%20matching%20the%20value%20with%20one%20of%20the%20rows%20from%20B3%3AB9000%20If%20a%20match%20is%20found%20I%20then%20want%20the%20value%20contained%20in%20column%20C%20%E2%80%9CParts%20description%E2%80%9D%20to%20be%20placed%20in%20the%20corresponding%20cell%20in%20the%20workbook%20%E2%80%9Cestimate%20universal%20mod%E2%80%9D%20IE%20%E2%80%9CC49%2C%20C50%2C%20C51%2C%20C52%2C%20C53%2C%20C54%2C%20C55%2C%20C56%2C%20C57%2C%20C58%2C%20C59%2CC49%2CC50%2CC51%2CC52%2CC53%2CC54%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20may%20make%20sense%20expressed%20as%20a%20formula!%20%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(B49%2C'%5BOps%20Center.xlsm%5DParts'!%24B%243%3A%24C%249000%2C2%2CFALSE)%3C%2FSTRONG%3E%20The%20formula%20would%20be%20placed%20in%20C49%2C%20C50%20and%20so%20on.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20complicate%20things%20a%20little%20further%20I%20also%20want%2C%20once%20a%20quantity%20is%20entered%20into%20cells%20D49%2C%20D50%2C%20D51%2C%20D52%2C%20D53%2C%20D54%2C%20D55%2C%20D56%2C%20D57%2C%20D58%2C%20D59%2CI49%2CI50%2CI51%2CI52%2CI53%2CI54%20%26nbsp%3Ba%20macro%20to%20input%20the%20price%20multiplied%20by%20the%20quantity%20in%20the%20cell%20directly%20to%20the%20right%20%E2%80%9CE49%2C%20E50%2C%20E51%2C%20E52%2C%20E53%2C%20E54%2C%20E55%2C%20E56%2C%20E57%2C%20E58%2C%20E59%2CJ49%2CJ50%2CJ51%2CJ52%2CJ53%2CJ54%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20explained%20as%20a%20vlookup%20for%20the%20first%20cell%20would%20be%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVLOOKUP(B49%2C'%5BOps%20Center.xlsm%5DParts'!%24B%243%3A%24F%249000%2C5%2CFALSE)*D49%3C%2FSTRONG%3E%20and%20the%20look%20up%20would%20be%20placed%20in%20cell%20E49%2C%20E50%20etc%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20also%20have%20different%20mark%20up%20prices%20for%20customers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20also%20be%20possible%20to%20have%20the%20lookup%20change%20depending%20on%20the%20value%20in%20cell%20Q17%20(Drop%20down%20list)%20IE%20if%20%22COST%22%20or%20%2210%25%22%20is%20selected%20in%20the%20drop%20down%20list%20then%20it%20would%20return%20the%20result%20from%20that%20column.%20I%20have%20attached%20the%20workbook%20which%20I%20am%20looking%20up!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20makes%20sense!%20Estimate%20workbook%20attached%20and%20the%20workbook%20i%20am%20looking%20up%20from.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-206811%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20%26amp%3B%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207327%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20as%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207327%22%20slang%3D%22en-US%22%3ETake%20the%20quotes%20out.%20The%20formula%20is%20seeing%20them%20as%20text%20rather%20than%20numbers.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207325%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20as%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207325%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Matt%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20struggling%20with%20the%20formula%20below%20which%20refuses%20to%20see%20numbers%20in%20my%20drop%20down%20list%20cell%20Q19%20if%20I%20use%20text%20ie%20%22TEN%22%20%22TWENTY%22%20it%20works%20fine%20but%20using%20numbers%20%2210%22%20%2220%22%20the%20formula%20does%20not%20seem%20to%20see%20them%20and%20does%20nothing.%20Is%20there%20a%20quick%20fix%3F%20perhaps%20a%20formatting%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3D(IF(Estimate!Q19%3D%2210%22%2CA2%2CIF(Estimate!Q19%3D%2220%22%2CB2%2CIF(Estimate!Q19%3D%2225%22%2CC2%2CIF(Estimate!Q19%3D%2223%22%2CD2%2CIF(Estimate!Q19%3D%2230%22%2CE2%2CIF(Estimate!Q19%3D%2250%22%2CF2%2CIF(Estimate!Q19%3D%2260%22%2CG2%2C))))))))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206991%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20as%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206991%22%20slang%3D%22en-US%22%3E%3CP%3EHey%20Mark-%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENice%20to%20see%20you%20on%20the%20forum%20again.%26nbsp%3B%20You%20can%20use%20VLookup%20in%20VBA%20code%20like%20the%20following...%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20VlookupSyntax()%0A%0A%20%20%20%20%20%20%20%20MyVariable%20%3D%20Application.WorksheetFunction.VLookup(Range(%22A2%22)%2C%20Range(%22%24A%242%3A%24J%241000%22)%2C%203%2C%20False)%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20'or%20WorksheetFunction%20isn't%20necessary%20if%20you%20know%20how%20to%20use%20the%20function%0A%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20MyVariable%20%3D%20Application.VLookup(Range(%22A2%22)%2C%20Range(%22%24A%242%3A%24J%241000%22)%2C%203%2C%20False)%0A%20%20%20%20%20%20%20%20%0AEnd%20Sub%0A%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1823075%22%20slang%3D%22en-US%22%3ERe%3A%20vlookup%20as%20a%20macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1823075%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F37127%22%20target%3D%22_blank%22%3E%40Matt%20Mickle%3C%2FA%3E%26nbsp%3BHello%20-%20i%20saw%20this%20post%20as%20part%20of%20a%20google%20search.%20I%20am%20trying%20to%20create%20a%20code%20that%20looks%20up%20an%20identification%20id%20number%20on%20one%20worksheet%2C%20and%20finds%20the%20corresponding%20data%20to%20it%20on%20another%20sheet.%20I%20will%20end%20up%20with%20columns%20of%20data%20corresponding%20to%20different%20entry%20times%20in%20my%20data%20sheet.%20I%20have%20the%20movement%20flow%20of%20the%20formula%20down%2C%20but%20when%20i%20got%20to%20run%20the%20code%20consecutively%2C%20it%20seems%20that%20my%20vlookup%20formula%20continues%20to%20move%20right%20in%20the%20spreadsheet%2C%20instead%20of%20holding%20constant%20at%20the%20identification%20id%20cell.%20I%20am%20using%20the%20formula%20below.%20do%20you%20know%20how%20to%20hold%20the%20cell%20constant%2C%20so%20that%20it%20will%20always%20look%20up%20the%20same%20ID%20number%3F%20The%20part%20i%20am%20having%20issues%20with%20is%20the%20RC%5B-4%5D%2C%20i%20would%20need%20it%20to%20increase%20every%20time%20(-4%2C-5%2C-6%2Cetc...)%20if%20i%20leave%20it%20in%20this%20format...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActiveCell.Formula%20%3D%20%22%3DVLOOKUP(RC%5B-4%5D%2C'Data%20Import'!R3C5%3AR2226C6%2C2%2CFALSE)%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I wish to use a lookup using a macro rather than a formula the data I wish to look up is contained within another workbook named “Ops centre” which is not open the data is on a sheet named “parts” in this workbook.

 

The open work book performing the look up is called “estimate universal mod” when a value is entered into any of the following cells  “B49, B50, B51, B52, B53, B54, B55, B56, B57, B58, B59,G49,G50,G51,G52,G53,G54” which I would make active so macro runs on data entry.

 

I want the look up to check the value entered against the closed workbook “Ops centre” sheet “Parts” matching the value with one of the rows from B3:B9000 If a match is found I then want the value contained in column C “Parts description” to be placed in the corresponding cell in the workbook “estimate universal mod” IE “C49, C50, C51, C52, C53, C54, C55, C56, C57, C58, C59,C49,C50,C51,C52,C53,C54”

 

This may make sense expressed as a formula!  

=VLOOKUP(B49,'[Ops Center.xlsm]Parts'!$B$3:$C$9000,2,FALSE) The formula would be placed in C49, C50 and so on.

 

To complicate things a little further I also want, once a quantity is entered into cells D49, D50, D51, D52, D53, D54, D55, D56, D57, D58, D59,I49,I50,I51,I52,I53,I54  a macro to input the price multiplied by the quantity in the cell directly to the right “E49, E50, E51, E52, E53, E54, E55, E56, E57, E58, E59,J49,J50,J51,J52,J53,J54”

 

This explained as a vlookup for the first cell would be

=VLOOKUP(B49,'[Ops Center.xlsm]Parts'!$B$3:$F$9000,5,FALSE)*D49 and the look up would be placed in cell E49, E50 etc

 

We also have different mark up prices for customers.

 

Would it also be possible to have the lookup change depending on the value in cell Q17 (Drop down list) IE if "COST" or "10%" is selected in the drop down list then it would return the result from that column. I have attached the workbook which I am looking up!

 

 

 

I hope this makes sense! Estimate workbook attached and the workbook i am looking up from.

4 Replies

Hey Mark-

 

Nice to see you on the forum again.  You can use VLookup in VBA code like the following...

 

Sub VlookupSyntax()

        MyVariable = Application.WorksheetFunction.VLookup(Range("A2"), Range("$A$2:$J$1000"), 3, False)
        
        'or WorksheetFunction isn't necessary if you know how to use the function
        
        MyVariable = Application.VLookup(Range("A2"), Range("$A$2:$J$1000"), 3, False)
        
End Sub

Thanks Matt

 

Also struggling with the formula below which refuses to see numbers in my drop down list cell Q19 if I use text ie "TEN" "TWENTY" it works fine but using numbers "10" "20" the formula does not seem to see them and does nothing. Is there a quick fix? perhaps a formatting issue?

 

=(IF(Estimate!Q19="10",A2,IF(Estimate!Q19="20",B2,IF(Estimate!Q19="25",C2,IF(Estimate!Q19="23",D2,IF(Estimate!Q19="30",E2,IF(Estimate!Q19="50",F2,IF(Estimate!Q19="60",G2,))))))))

 

Take the quotes out. The formula is seeing them as text rather than numbers.

@Matt Mickle Hello - i saw this post as part of a google search. I am trying to create a code that looks up an identification id number on one worksheet, and finds the corresponding data to it on another sheet. I will end up with columns of data corresponding to different entry times in my data sheet. I have the movement flow of the formula down, but when i got to run the code consecutively, it seems that my vlookup formula continues to move right in the spreadsheet, instead of holding constant at the identification id cell. I am using the formula below. do you know how to hold the cell constant, so that it will always look up the same ID number? The part i am having issues with is the RC[-4], i would need it to increase every time (-4,-5,-6,etc...) if i leave it in this format...

 

ActiveCell.Formula = "=VLOOKUP(RC[-4],'Data Import'!R3C5:R2226C6,2,FALSE)"