Forum Discussion

mark ainscough's avatar
mark ainscough
Brass Contributor
Jun 21, 2018

vlookup as a macro

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.

Macros & VBA
Need Help

4 Replies

Sort By
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 22, 2018

    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
    • mark ainscough's avatar
      mark ainscough
      Brass Contributor
      Jun 24, 2018

      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,))))))))

       

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jun 24, 2018
        Take the quotes out. The formula is seeing them as text rather than numbers.

Resources