VBA Extract and sum numeric values from bracket

[nicolastella]

Hello, I had a code for this but I can find it anymore.
Basically, I have a sheet with a column of alphanumeric values. At the end of each string I have a decimal value between brackets, but also some text. I would like to extract the numeric value only, sum all the extracted values with the result on the next empty cell.
Returned value in B4, B8, B10.

Please see example below:


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DESCRIPTION[/TD]
[TD]TOT.[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]sheet (2.340 sf)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]sheet (12.480 sf)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]14.82[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]plate (23.120 sf)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]plate (3.200 sf)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]plate (4.120 sf)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]30.44[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]bar (12.230 in)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]12.23[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Split(Replace(Cl.Value, "(", ""))(1)
      Next Cl
      Rng.Offset(Rng.Count, 1).Resize(1, 1).Value = MySum
      MySum = 0
   Next Rng
End Sub

 

[Rick Rothstein]

Hi & welcome to MrExcel.
How about

Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Split(Replace(Cl.Value, "(", ""))(1)
      Next Cl
      Rng.Offset(Rng.Count, 1).Resize(1, 1).Value = MySum
      MySum = 0
   Next Rng
End Sub

Your code assumes the text before the open parenthesis will always be a single word... if it were two or more words (I am thinking of something like, say, "iron plate"), your code errors out. Here is a revision which handles that problem (as long as the opening parenthesis around the number is the first open parenthesis in the text) while compacting your MySum assignment code line slightly...

[table="width: 500"]
[tr]
	[td]Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Val(Mid(Cl, 1 + InStr(Cl, "(")))
      Next Cl
      Rng.Offset(Rng.Count, 1)(1).Value = MySum
      MySum = 0
   Next Rng
End Sub[/td]
[/tr]
[/table]

 

[nicolastella]

Thanks Fluff and Rick for your fast reply.
The Rick's code probably will work better for my scope, however, I forgot to mention that value in between brackets has first to be multiply with the number next to it (q.ty) and then add to total.
Please see sample below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]DESCRIPTION[/TD]
[TD]Q.TY[/TD]
[TD]TOT.[/TD]
[/TR]
[TR]
[TD]SHEET METAL SS304 7GA (25.00 SF)[/TD]
[TD]2[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]SHEET METAL SS304 7GA (5.00 SF)[/TD]
[TD]1[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]SHEET METAL SS304 7GA (10.00 SF)[/TD]
[TD]3[/TD]
[TD]15.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]70.00[/TD]
[/TR]
[TR]
[TD]SHEET METAL SS304 7GA (25.00 SF)[/TD]
[TD]2[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]SHEET METAL SS304 7GA (10.00 SF)[/TD]
[TD]1[/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]60.00[/TD]
[/TR]
</tbody>[/TABLE]

Return values in column C. Partial values can be displayed or not, I just need to see the total (C4 , C7...)
If someone can help me with this one as well I will really appreciate.

Thanks

 

[Fluff]

How about

Sub nicolastella()
   Dim Rng As Range, Cl As Range
   Dim MySum As Double
   
   For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
      For Each Cl In Rng
         MySum = MySum + Val(Mid(Cl, 1 + InStr(Cl, "("))) * Cl.Offset(, 1)
      Next Cl
      Rng.Offset(Rng.Count, 2)(1).Value = MySum
      MySum = 0
   Next Rng
End Sub

 

[nicolastella]

This works perfectly!! You guys are the best.
Now, I used to run this code from Autodesk Inventor VBA linked to an Excel sheet rather than run it directly with Excel.
Doing this it give me a compile error (expect variable or procedure, not module) for the .SpecialCells(XlConstants).Areas.
I'm guessing this is happening because I didn't load the Excel object library. Can be this the case?

 

[Fluff]

I've absolutely no idea, as I've never heard of AutoDesk Inventor.
So unfortunately cannot help with that.

 

[nicolastella]

Ok, I just changed from .SpecialCells(xlConstants) to .SpecialCells(xlCellTypeConstants) and it works from Inventor VBA.
Thanks all for your help,
Nicola

 

[Fluff]

You're welcome & thanks for the feedback