Loop Through Visible Cells In Column And If It Contains Specific Text Sum Next Col

Hello Everyone,

I am now getting to the end of my current project but am stuck on a somplicated (for me) problem. My sheet it filtered to show a week number then in column A there are a number of origin names such as Hong Kong, HK & Yantian, CN and in column B there are cubic meaturements for each
origin. I now have to add up the cubic measurement for each origin name then place on another sheet. Its the adding up but I think I am having trouble with. I would really appreciate any help specially as i'm so close to finishing. I know the code below is pretty rough but you get the idea.

Code:

---

Dim oCell as Range
Range(Range("A5"), Range("A5").End(xlDown)).Select
    For Each oCell In Selection
    If oCell.Text = "*Hong Kong*" Then
    ' add the figure in next cell to the right with all the next ocell (b column) values   
    End If
    Next oCell

---

Once all are added cubic figures are added togther I guess I need to declare an integer variable to set the result to?

I can't quite believe I am posting this but I have managed to sort this out myself :).

Code:

---

Dim oSum1 As Double
    Range(Range("A5"), Range("A5").End(xlDown)).SpecialCells(xlVisible).Select
    For Each oCell In Selection
    If InStr(1, oCell.Text, "Hong Kong") Then
    oSum1 = oSum1 + oCell.Offset(0, 12).Value
    End If
    Next oCell

---

You can also use the subtotal function.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,ROW(C2:C22)-ROW(C2),,,1)),--(ISNUMBER(SEARCH("Hong Kong",B2:B22))))

adjust the range.