Results 1 to 3 of 3

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

  1. #1

    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?

  2. #2
    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

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 6
    Last Post: 07-26-2013, 11:42 AM
  2. Replies: 9
    Last Post: 07-21-2013, 05:50 PM
  3. Replies: 1
    Last Post: 06-12-2013, 07:42 PM
  4. Replies: 3
    Last Post: 05-23-2013, 11:17 PM
  5. Replies: 4
    Last Post: 05-05-2013, 04:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •