match column for value and return next cell value

K

Koganti

Hi,

I have simple excel with one column having the Part name and secon
having the Price.

I want a macro which will return SUM of all the part prices user ha
selected.

My data looks simply like below

PART NAME PRICE
NKT 1000
NKU 2000
NMP 150
NPG 299
NWT 3495


What i want is if user enters NKT,NKU then it should return 3000

if user enters NKT,NKu,NMP then 3150.

Can anyone please help as I am an end user and have no idea of macros.

Thanks,
kogant
 
K

kkknie

This should do it:

Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim strTemp As String
Dim strLook As String
Dim iFound As Long
Dim r As Range
Dim rTotal As Double

If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then 'one cell selected

If Target.Address = "$C$1" Then 'Range C1 Entered
strTemp = Range("C1").Value

rTotal = 0
Do Until strTemp = ""

iFound = InStr(1, strTemp, ",")
If iFound > 0 Then
strLook = Trim(Left(strTemp, iFound - 1))
strTemp = Trim(Right(strTemp, Len(strTemp) - InStr(1, strTemp, ",")))
Else
strLook = Trim(strTemp)
strTemp = ""
End If

For Each r In Range("A2:A6")
If UCase(r.Value) = UCase(strLook) Then
rTotal = rTotal + Cells(r.Row, 2).Value
End If
Next

Loop

Range("D1").Value = rTotal

End If

End If

End Sub

-------------------

The following will need to be changed:
- Range("C1") should be where the user types part names
- Target.Address = "$C$1" should be where the user types part names
- Range("D1") should be where the sum is returned
- Range("A2:A6") should be the range of part numbers
- In Cells(r.Row, 2).Value, the 2 should be the column number with th
prices.

The code also assumes that commas only will be used to separate par
names.

I put the code in the Worksheet_Change event so whenever the cell i
changed, it will run. Paste into the code for the sheet (not th
workbook or a module).
 
K

Koganti

kkknie,

Thanks for your reply. Where to paste this code. Is it new macro I nee
to create???

Please help.

Ko
 
F

Frank Kabel

Hi
this is no code?
this is a build-in feature of Excel. It can be found in the menu
'Data - Pivot table'
I think the sites give some good introdutions how to getting started
with this feature :)
 
K

kkknie

Koganti,

Frank is most probably right about using a pivot table. I have no
learned enough about them, so seldom suggest them. You may want t
learn how (and then school me a bit). Plus, if you don't really wan
to learn about macros (VBA), pivot tables are more for end-users tha
code is.

As for my code, right click on the tab for the sheet you wish to wor
with and select View Code. Paste the code there.

Sorry so late responding,
 
Top