Calculate based on number between text and search on text

lauradelfosse 

Since I'm not very good with Belgian formulas, here is an example with VBA.

Code is not tested, please backup your file beforehand.

Sub CalculateInterventionsAndInstallations()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim startPos As Long
    Dim endPos As Long
    Dim interventionSum As Double
    Dim installationSum As Double
    Dim numWeeks As Long
    Dim tempValue As Double
    Dim textToSearch As String
    
    ' Set your worksheet and range here
    Set ws = ThisWorkbook.Sheets("Berekeningen")
    Set rng = ws.Range("B3:F64") ' Adjust this range as needed

    ' Initialize sums
    interventionSum = 0
    installationSum = 0
    numWeeks = 9 ' Set the number of weeks

    ' Loop through each cell in the range
    For Each cell In rng
        If Not IsEmpty(cell.Value) Then
            ' Search and sum for interventions ("AANWEZIG" and "INI")
            textToSearch = cell.Value
            startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare)
            If startPos > 0 Then
                endPos = InStr(startPos, textToSearch, "INI", vbTextCompare)
                If endPos > startPos Then
                    tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9)
                    If IsNumeric(tempValue) Then
                        interventionSum = interventionSum + CDbl(tempValue)
                    End If
                End If
            End If

            ' Search and sum for installations ("AANWEZIG" and "INS")
            startPos = InStr(1, textToSearch, "AANWEZIG", vbTextCompare)
            If startPos > 0 Then
                endPos = InStr(startPos, textToSearch, "INS", vbTextCompare)
                If endPos > startPos Then
                    tempValue = Mid(textToSearch, startPos + 9, endPos - startPos - 9)
                    If IsNumeric(tempValue) Then
                        installationSum = installationSum + CDbl(tempValue)
                    End If
                End If
            End If
        End If
    Next cell

    ' Output results to specific cells
    ws.Range("G2").Value = interventionSum
    ws.Range("G3").Value = interventionSum / numWeeks
    ws.Range("G4").Value = installationSum
    ws.Range("G5").Value = installationSum / numWeeks
    
    MsgBox "Calculations completed successfully!"
End Sub

I hope this helps you with your project.