I work on an electric company that builds power distribution systems that uses standard electrical structures with predefine materials on it.Every structure has an unique identifier.

So I made a workbook with 3 worksheets on it.
The first one is a table of each pole with their respective list structures on it, that is generated by a autocad drawing.

(example: Pole 1 has structure A, B, C.
          Pole 2 has structure B, D, F
          Pole 3 has structure A, C, F)

The second worksheet has a table that contains columns with the standard materials that all structures have.

(example                 A    B   C   D   F
         Material 1      1    0   0   1   1
         Material 2      0    0   3   0   1
         Material 3      2    2   1   0   0 
         Material 4      0    1   0   4   0 )

The third worksheet contains a table with the sum of all the material of each pole.

(example                 Pole 1    Pole2   Pole3
                         A+B+C     B+D+F   A+C+F
         Material 1        1         2       2   
         Material 2        3         1       4   
         Material 3        5         2       3   
         Material 4        1         5       0   )  

I made a macro on excel VBA that iterate every structure present on worksheet 1, then finds the range of materials on worksheet 2 and sums the total on worksheet 3, but is very slow.

Usually I have at least 100 poles with 7 or 8 structures on every one, and every structure has 50 items on it, so it takes up to 8 minutes to do simple sums. and the material database has up to 500 structures listed there.

PD. Sorry for my english.

Here is some code that I use.

Sub Iteration()
    Dim sourcews As Worksheet
    Dim targetws As Worksheet
 
    Dim rowCount As Long
    Dim colCount As Long
    Dim r As Long, c As Long
    Dim cellValue As Integer
    
    
    Set sourcews = ThisWorkbook.Sheets("WORKSHEET1") 
    
    
    rowCount = sourcews.Cells(sourcews.Rows.Count, 1).End(xlUp).Row 
    colCount = sourcews.Cells(1, sourcews.Columns.Count).End(xlToLeft).Column 


    
    For r = 1 To rowCount
        
            For i = 1 To 25
                If sourcews.Cells(r, i + 10).Value <> "" Then
                    cellValue = Right(sourcews.Cells(r, 5).Value, Len(sourcews.Cells(r, 5).Value) - 1)
                    Call SumColumnData(cellValue, sourcews.Cells(r, 5).Value, sourcews.Cells(r, i + 10).Value)
                End If
            Next i
        
    Next r



End Sub


Sub SumColumnData(cellPost As Integer, numPost As String, estr As String)

    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim headerName As String
    Dim targetHeader As String
    Dim sourceColumn As Range
    Dim targetColumn As Range
    Dim sourceHeaderCell As Range
    Dim cell As Range
    Dim lastRowSource As Long
    Dim lastRowTarget As Long
    Dim targetHeaderCell As Range
    
    
    
    ' Set references to the sheets
    Set sourceSheet = ThisWorkbook.Sheets("WORKSHEET2") 
    Set targetSheet = ThisWorkbook.Sheets("WORKSHEET3") 

    ' Find the column with the specified header in the source sheet
        If Right(estr, 1) = "r" Then
            Set sourceHeaderCell = sourceSheet.Rows(1).Find(What:=Left(estr, Len(estr) - 1), LookIn:=xlValues, LookAt:=xlWhole)
        Else
            Set sourceHeaderCell = sourceSheet.Rows(1).Find(What:=estr, LookIn:=xlValues, LookAt:=xlWhole)
        End If
    
    
    If Not sourceHeaderCell Is Nothing Then
        ' Get the range for the column below the header
        lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, sourceHeaderCell.Column).End(xlUp).Row
        Set sourceColumn = sourceSheet.Range(sourceHeaderCell.Offset(1, 0), sourceSheet.Cells(lastRowSource, sourceHeaderCell.Column))
        
        'Find the column with the specified header in the target sheet
        If Right(estr, 1) = "r" Then
            Set targetHeaderCell = targetSheet.Range("A1").Offset(0, cellPost * 2 + 8)
        Else
            Set targetHeaderCell = targetSheet.Range("A1").Offset(0, cellPost * 2 + 7)
        End If
        
        If Not targetHeaderCell Is Nothing Then
            ' Get the range for the target column
            lastRowTarget = targetSheet.Cells(targetSheet.Rows.Count, targetHeaderCell.Column).End(xlUp).Row
            Set targetColumn = targetSheet.Range(targetHeaderCell.Offset(13, 0), targetSheet.Cells(lastRowTarget, targetHeaderCell.Column))
            
            ' Sum the values from the source column and add to the target column
            For Each cell In sourceColumn
                If IsNumeric(cell.Value) Then
                    targetSheet.Cells(cell.Row + 12, targetHeaderCell.Column).Value = targetSheet.Cells(cell.Row + 12, targetHeaderCell.Column).Value + cell.Value
                End If
            Next cell

            
        End If

    End If

End Sub

Could you help me do this more efficient. I think accesing the worksheet 2 every time I have to find every structure and the sum on worksheet 3 make it so slow.

I read I should use arrays instead of ranges to store the base of materials but I dont know how to do that.