finding when a string occurs inside a cell in excel

I have a spreadsheet with a bunch of numerical data, all arranged in one column. The data is broken up by the use of a keyword.

KEYWORD
1
2
3
4
5
KEYWORD
1
2
KEYWORD
.
.
.
开发者_Python百科

I was wondering if there was a way to quickly take the averages between each instance of the keyword, and set it in the cell to the right of the area that was just averaged. I have never done any real hardcore VBA scripting, so I am totally at a loss.

This should do it. There may be a better way, though.

Sub GetAverages()
    Dim sum As Single
    Dim count As Long
    Dim cell As Range

    For Each cell In ActiveWorkbook.ActiveSheet.Range("a1", ActiveSheet.Range("A1").End(xlDown))
        If CStr(cell.Value) = "KEYWORD" Then
            If count > 0 Then
                ActiveWorkbook.ActiveSheet.Cells(cell.Row - 1, cell.Column + 1).Value = sum / count
            End If

            count = 0
            sum = 0
        Else
            sum = sum + cell.Value
            count = count + 1
        End If
    Next cell
End Sub

It outputted this on my sample data...

KEYWORD 
92  
20  
93692   
936 
92         18966.4
KEYWORD 
32  
324        178
KEYWORD 
235 
324 
23  
3   
342 
2   
343 
34         163.25
KEYWORD 

I would not use VBA.

Use this formula in column next to the column with values:

=IF(ISNUMBER(<columnName>);<columnName>;"")

For example if you column with values is C then

=IF(ISNUMBER(C1);C1;"")

Then use the Average function on the new column:

=AVERAGE(D:D)