VBA to populate formula in a cell based on value on the cell 4 cells upwards

[justme101]

Hello all,

I don't think the subject line made sense, so I will explain the situation here more clearly. Please have a look at the snapshot of the table below:

What I need to do in the file, is loop through column B, find cells which have "Total Volume" written in them, if there is a match ONLY then search if the value in the cells adjacent to DESIGNATION has the values AVP. If there is a match for this, then the value for that cell in the row for TOTAL VOLUME will have a certain formula, and if it is not AVP then just move to the next cell.

This needs to be repeated for each set of data (read criteria B4 through B8 as a set, and each DESIGNATION row will have values similar to row 8). I have written this code, but it does not seem to work for me.

Sub update_volume_formulas_retail()

ThisWorkbook.Worksheets("Retail").Activate

Dim lr1 As Long, rng As Range, rng1 As Range
lr1 = Cells(Rows.Count, "B").End(xlUp).Row   'Assuming Attributes are in Column B

On Error Resume Next
For Each rng In ActiveSheet.Range("B11:B" & lr1)
        If rng.Value = "Total Volume" Then
        rng.Offset(0, 4).Select
        Set rng1 = Range(Selection, Selection.Offset(0, 14)).Select
            For Each rng1 In ActiveSheet.Selection
            If InStr(rng1.Offset(-5, 0).Value, "AVP") Or InStr(rng1.Offset(-5, 0).Value, "avp") Then
                MsgBox ("Found")
                rng1.Formula = "xxxx" 'putting x's here, as I can insert the formula later and can't show what it is. You may put any formula here just for testing
            End If
            Next
        End If
Next

End Sub