Solved

vba copy values into new column using vlookup

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…

786 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