Macro to calculate totals and collate lists into different cells

[aydanrogers]

Hello all,

I am after a macro that will find certain text in column A (eg. 1D, 17, 37A) the take the text from that cell and collate it into a list in another cell (example: A1 contains"1D - Eggs", A15 contains "17 - Chickens", A28 contains "37A - Turkeys") then placing that text into another cell (eg. 1D - Eggs, 17 - Chickens, 37A - Turkeys).

The next part of the macro will then take the number figure from column B next to the cells identified from the first part and total the number and paste that number into a cell (eg. B1 + B15 + B28 cell numbers = 28)

Hopefully this makes sense to you all, I am hopeless at making macros from scratch like this

Cheers in advanced

Aydos

 

[DanteAmor]

Try the following, in the cell write the "certain texts" separated by commas.
Run the macro and the results will be in cells E2 and F2:

Dante Amor
	A	B	C	D	E	F
1	TEXT	VALUE		certain text	Result	Sum
2	1D - Eggs	1		1D, 17, 37A	1D - Eggs - 17 - Chickens - 37A - Turkeys	14
3	data	2
4	data	3
5	17 - Chickens	4
6	data	5
7	data	6
8	data	7
9	data	8
10	37A - Turkeys	9
11
Hoja1

Sub calculate_totals()
  Dim sText As String, cad As String, s As Variant
  Dim f As Range, sum As Double
  
  sText = Range("D2").Value
  For Each s In Split(sText, ",")
    Set f = Range("A:A").Find(Trim(s), , xlValues, xlPart, , , False)
    If Not f Is Nothing Then
      cad = cad & f & " - "
      sum = sum + f.Offset(, 1)
    End If
  Next
  If cad <> "" Then Range("E2").Resize(1, 2).Value = Array(Left(cad, Len(cad) - 3), sum)
End Sub