VBA Code Fix - calculating a group of rows, excluding amount if column contains keywords

[lichldo]

Hello, I have the following code that is almost working, but I can't crack the last bit.

I have a sheet where I have groups of rows (meaning a blank row in between each set) and in that blank row, I want to total the values of column N, but exclude the value from the total if column F contains the text "pre-sales" or "non-billable". There might be other text in F, but if it contains those keywords at all, it needs to be excluded.

The code works to total the groups I want, put it in the correct place, and with the correct formatting - but it is not excluding the values that have those keywords in F.

Sub ProjectTotals()
    Dim i As Long
    Dim lrow As Long
    Dim PrevRow As Long
    Dim j As Long
    
    lrow = Cells(Rows.Count, 1).End(xlUp).Row
    PrevRow = 1
    
    For i = 2 To lrow - 1 ' Exclude rows 1 and the last row with text
        If InStr(1, Cells(i, 6), "pre-sales") = 0 And InStr(1, Cells(i, 6), "non-billable") = 0 Then ' Exclude rows with "pre-sales" or "non-billable" in column F
            If InStr(1, Cells(i, 14), "pre-sales") = 0 And InStr(1, Cells(i, 14), "non-billable") = 0 Then ' Exclude rows with "pre-sales" or "non-billable" in column N
                If Cells(i, 14) = "" Then
                    Range("N" & i).Formula = "=SUM(N" & PrevRow + 1 & ":N" & i - 1 & ")"
                    Range("N" & i).Copy
                    Range("N" & i).PasteSpecial xlPasteFormulasAndNumberFormats
                    Application.CutCopyMode = False
                    
                    PrevRow = i
                    
                    Range("N" & i).Interior.Color = RGB(255, 255, 0) ' Yellow color
                    Range("N" & i).Font.Bold = True
                End If
            End If
        End If
    Next i
End Sub

 

[DanteAmor]

Hi @lichldo . thanks for postion on MrExcel.

Try the following:

Sub ProjectTotals()
  Dim i As Long
  Dim tot As Double
  
  For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row - 1 ' Exclude rows 1 and the last row with text
    If InStr(1, LCase(Range("F" & i).Value), "pre-sales") = 0 And InStr(1, LCase(Range("F" & i).Value), "non-billable") = 0 Then  ' Exclude rows with "pre-sales" or "non-billable" in column F
      With Range("N" & i)
        tot = tot + .Value
        If .Value = "" Then
          .Value = tot
          .Interior.Color = RGB(255, 255, 0) ' Yellow color
          .Font.Bold = True
          tot = 0
        End If
      End With
    End If
  Next i
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​