Solved

vba copy values into new column using vlookup

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

751 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