code to count # of cells between activecell and a target cell

[blowy]

Hello,

I'm trying to write a macro to save me a huge amount of time at work. My VBA knowledge, however, is all self taught (over the last week), and somewhat limited.

The macro needs to check A2:A458 for every instance of the string: "word2". Upon finding a cell containing "word2" I want it to count the number of cells above it until it identifies a cell containing the string: "word1". This number (e.g. if word1 is 3 cells above word2, then the number is 3) is then compared to a table of ten possible values (1-10). At this point I intend to write an if statement indicating what happens for each of the 10 possible values. So, if 3, then I would add 2,250,000 microseconds to a time in a separate sheet.

I believe I've figured out most of the code, but I can't find a way to count the distance between "word2" and "word1". If I can get this macro to work, it will save me weeks of work, so I would be grateful for any and all help. This is also my first time using this forum, so please forgive any faux pas I may have committed.

Thanks,
Ben

 

[tweedle]

Welcome!

... I want it to count the number of cells above it until ...

The Code loops from the bottom to the top, finds the keywords, calcs the row.delta and provides for your math/updates.
Assumes that the keywords alternate as described else undesireable results may occur.

Sub LoopCells()
Dim ws As Worksheet
Set ws = Sheets("Sheet2") 'Adjust for your sheet name
 
LastRow = Range(Cells(ws.Rows.Count, "A").Address).End(xlUp).Row
For rowID = LastRow To 2 Step -1
    If InStr(1, LCase(Cells(rowID, "A").Value), "word2", vbTextCompare) > 0 Then
        word2row = rowID
    ElseIf InStr(1, LCase(Cells(rowID, "A").Value), "word1", vbTextCompare) > 0 Then
        word1row = rowID
        Delta = word2row - word1row: Debug.Print word2row; " - "; word1row; "="; Delta
 
        Select Case Delts
            Case Is = 1
                'your math here
            Case Is = 2
            Case Is = 3
            Case Is = 4
            Case Is = 5
            Case Is = 6
            Case Is = 7
            Case Is = 8
            Case Is = 9
            Case Is = 10
            Case Else
        End Select
        word2row = 0: word1row = 0 'clear our last values
    End If
Next rowID
End Sub

 

[tweedle]

type oh
Select Case Delts
s/b
Select Case Delta

 

[blowy]

Fantastic! Thank you so much for the help. I found a work around for my code, but this is much more efficient.