Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vba copy values into new column using vlookup

Posted on 2006-07-23
3
Medium Priority
?
268 Views
Last Modified: 2010-04-30
Hi,
I have one column on my Excel worksheet with a long list of part numbers (PartNumer). I have another new set of columns copied over from another worksheet that contains the same part numbers (sorted in ascending order) and another set of values (Cost). Is there a way to paste into a 2nd col. the Cost only if the part numbers from the new column match up with the ones on the sheet (search for all rows)?  Is Vlookup a good method? how do I use it?. thanks a bunch :)
0
Comment
Question by:azcalv408
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Expert Comment

by:PSSUser
ID: 17163029
Yes VLOOKUP is a good method.

If the list of part numbers and the list of part number and prices is on sheet2 in columns A and B and your list of part numbers you want to find a price for is in column A (starting in cell A2) on sheet1 then in cell B2 on sheet 1 put this formula:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A:$B,2,FALSE)),"",VLOOKUP(A2,Sheet2!$A:$B,2,FALSE))

This will then leave cell B2 blank is the price isn't found (VLOOKUP returns an error) otherwise it will put the price in. You can then copy this down into all of the required cells.

Here is an explaination of what "VLOOKUP(A2,Sheet2!$A:$B,2,FALSE)" is telling Excel to do:
Parameter 1: Look up the value in cell A2 (the partnumber)
Parameter 2: This is cell references on the table containing the part number and price. The value we are looking for (part number) must be in the first column selected.
Parameter 3: When it finds part number in must return the value in the 2nd column (Price) of the columns we told Excel to use in parameter 2.
Parameter 4: By setting this to false this tells Excel to only return exact matches. If we set this to true and the list is sorted Excel would return the price of the part number before the one we are looking for if the one we are looking for doesn't exist which wouldn't make any sense.


Regards,

Chris
0
 

Author Comment

by:azcalv408
ID: 17163690
Chris,
    How do I translate the formula into vb codes, so it does the operation automatically for all cells (down to last partnumber)? thanks for helping. This is exactly what I needed :)
0
 
LVL 10

Accepted Solution

by:
PSSUser earned 500 total points
ID: 17166080
It depends if you have a list at the point you want to get the prices or if you want to fill the column in when a person types a part number in column A.

If you already have a complete list, making the same assumptions about data location as before then add the following code to a VBA module in your Excel spreadsheet:

Sub GetPrices()

On Error Resume Next
Dim lCnt As Long

'Start from first row of data - Assumes row 1 contains headers
lCnt = 2

'Move through records until we reach the last one (empty cell in column A)
Do Until Worksheets("Sheet1").Range("A" & CStr(lCnt)).Value = ""
  'Get the value of the VLOOKUP and put it in cell B
  Worksheets("Sheet1").Range("B" & CStr(lCnt)).Value = WorksheetFunction.VLookup(Worksheets("Sheet1").Range("A" & CStr(lCnt)).Value, Worksheets("Sheet2").Range("$A:$B"), 2, False)
  'Handle the VLOOKUP returning an error (Part Number not found in price list)
  If Err.Number = 1004 Then
    'Print a message saying the part number was not found. Alternatively you could leave the cell blank.
    Worksheets("Sheet1").Range("B" & CStr(lCnt)).Value = "Item not in price list"
  End If
  'Clear the error
  Err.Clear
  'Move on to the next row
  lCnt = lCnt + 1
Loop

End Sub


If you want to do the lookup when a part number is types into row A on sheet 1 then you will want to add the following code the the Sheet1 code section:


Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

'Check the change was in column 1 (A)
If Target.Column = 1 Then
  'Get the value of the VLOOKUP and put it in cell B
  Worksheets("Sheet1").Range("B" & CStr(Target.Row)).Value = WorksheetFunction.VLookup(Target.Value, Worksheets("Sheet2").Range("$A:$B"), 2, False)
  'Handle the VLOOKUP returning an error (Part Number not found in price list)
  If Err.Number = 1004 Then
    'Print a message saying the part number was not found. Alternatively you could leave the cell blank.
    Worksheets("Sheet1").Range("B" & CStr(lCnt)).Value = "Item not in price list"
  End If
  'Clear the Error
  Err.Clear
End If

End Sub

Regards,
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question