Solved

vba copy values into new column using vlookup

Posted on 2006-07-23
3
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now