Solved

vba copy values into new column using vlookup

Posted on 2006-07-23
3
266 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 125 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

624 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