Can I sum text values like numbers from a single cell?

[MrBadEXCEL]

Below is a screenshot of a workbook with a sample of what I am trying to do.
I have a sheet with a tab labeled "MATERIALS". The sheet contains a list of materials in column A and in column B is the corresponding value of the materials

On a second sheet with the tab labeled "PROJECTS" I have a column labeled "Project Materials". The cell A2 contains a dropdown list of all the materials referenced
in the MATERIALS sheet. When I select a material from the dropdown list it is added to cell A2 and separated with a comma. On my sheet example I have three materials
selected "Black Material, White Material, Red Material"

What I am trying to do is get the list of material I use for a project in cell A2 to sum up in cell B2. In the example the total cost should equal $6.50.

 

[kweaver]

This might be one option (no error checking for missing materials).

Sub CalcSum()
Dim i As Long, ary() As String, j As Long, tc As Double
For i = 2 To Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row
ary = Split(Sheets("Projects").Cells(i, 1) & ", ", ", ")
tc = 0
  For j = 0 To UBound(ary) - 1
  tc = tc + WorksheetFunction.VLookup(ary(j), Sheets("Materials").Range("A2:B10"), 2, False)
  Next j
Sheets("Projects").Cells(i, 2) = tc
Next i
Sheets("Projects").Columns("B").NumberFormat = "$#,#0.00"
End Sub

 

[MrBadEXCEL]

Thank you, I got this to work but I'm not sure how to make it calculate on it's own? Do I need to run the macro to get it to calculate each time i add a material to the project or can it be done automatically?

This might be one option (no error checking for missing materials).

Sub CalcSum()
Dim i As Long, ary() As String, j As Long, tc As Double
For i = 2 To Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row
ary = Split(Sheets("Projects").Cells(i, 1) & ", ", ", ")
tc = 0
  For j = 0 To UBound(ary) - 1
  tc = tc + WorksheetFunction.VLookup(ary(j), Sheets("Materials").Range("A2:B10"), 2, False)
  Next j
Sheets("Projects").Cells(i, 2) = tc
Next i
Sheets("Projects").Columns("B").NumberFormat = "$#,#0.00"
End Sub

 

[kweaver]

Yes, run the macro to do the calculations. An alternative would be to have the VBA changed to Worksheet Change Event then it will run every time any change is made.

 

[kweaver]

You would put the following on the sheet (not the module) and change the range accordingly.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, ary() As String, j As Long, tc As Double
If Not Intersect(Target, Sheets("Projects").Range("A1:A10")) Is Nothing Then
For i = 2 To Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row
ary = Split(Sheets("Projects").Cells(i, 1) & ", ", ", ")
tc = 0
  For j = 0 To UBound(ary) - 1
  tc = tc + WorksheetFunction.VLookup(ary(j), Sheets("Materials").Range("A2:B10"), 2, False)
  Next j
Sheets("Projects").Cells(i, 2) = tc
Next i
Sheets("Projects").Columns("B").NumberFormat = "$#,#0.00"
End If
End Sub

 

[MrBadEXCEL]

Also, could you tell me how to refer to a range of noncontiguous cells if I wanted to change column B to say E
And if my lists of materials expand, how do I write the range expand so it expands? A$2:A2 Not sure how to edit
this code to make those things work.

This might be one option (no error checking for missing materials).

Sub CalcSum()
Dim i As Long, ary() As String, j As Long, tc As Double
For i = 2 To Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row
ary = Split(Sheets("Projects").Cells(i, 1) & ", ", ", ")
tc = 0
  For j = 0 To UBound(ary) - 1
  tc = tc + WorksheetFunction.VLookup(ary(j), Sheets("Materials").Range("A2:B10"), 2, False)
  Next j
Sheets("Projects").Cells(i, 2) = tc
Next i
Sheets("Projects").Columns("B").NumberFormat = "$#,#0.00"
End Sub

 

[kweaver]

This line

If Not Intersect(Target, Sheets("Projects").Range("A1:A10")) Is Nothing Then

is looking at the range A1:A10. Change that to A1:A100 if you want. If your drop downs aren't in that range in column A, change the range accordingly.
But, then you'll have to modify the macro to reflect where the drop downs are.

 

[MrBadEXCEL]

This line

If Not Intersect(Target, Sheets("Projects").Range("A1:A10")) Is Nothing Then

is looking at the range A1:A10. Change that to A1:A100 if you want. If your drop downs aren't in that range in column A, change the range accordingly.
But, then you'll have to modify the macro to reflect where the drop downs are.

Got it...

If I skip a line in the range however, let's say I put a divider row between the range or just skip over a row, I am getting a Run-time error '1004': Method 'VLookup' of object 'WorksheetFuction' failed.

 

[kweaver]

Yep, that's because the macro is trying every row in the full range and middle rows won't match and can't be checked.
You'd need some test to avoid that.

 

[kweaver]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, ary() As String, j As Long, tc As Variant
If Not Intersect(Target, Sheets("Projects").Range("A1:A10")) Is Nothing Then
For i = 2 To Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row
ary = Split(Sheets("Projects").Cells(i, 1) & ", ", ", ")
tc = 0
  For j = 0 To UBound(ary) - 1
  If IsEmpty(Cells(i, 1)) Then
  tc = ""
  Else
  tc = tc + WorksheetFunction.VLookup(ary(j), Sheets("Materials").Range("A2:B10"), 2, False)
  End If
  Next j
Sheets("Projects").Cells(i, 2) = tc
Next i
Sheets("Projects").Columns("B").NumberFormat = "$#,#0.00"
End If
End Sub