VBA Beginner - searching a row for a specific value then count it
<p>I am relatively new to VBA and I am learning as I go, so my scripting probably isn't the best solution to the problem at hand.  I have a range of cells which I need to check a row of three columns for an occurrence of a string/value, if it appears at least once within the row of three columns then I need to add a count this, if not then I need to look for the next option which is another value, if that appears more than once count this occurrence and then finally check for the last option and add a count to that.  I want to then print the counts to a cell within the work sheet. </p>

<p>Unfortunately I wont know what the first row and last row number I will be searching is so I have a bit of script which will tell me these and assign to an integer, I know this works.  What doesn't work is the second part where I am searching the rows and looking for the value and adding a count.  It does print to the worksheet but all I get it three 0's.</p>

<p>I am not sure where in my code has gone wrong - compiles fine with no errors.</p>

<p>This is the scripting I have: -</p>

<pre><code>Public Sub counts()

    Dim redVarCount As Integer
    Dim amberVarCount As Integer
    Dim greenVarCount As Integer

    'Used to find the first and last occurrence of the type want to count colors for
    mediumStartRow = Range("H:H").Find(what:="Medium", after:=Range("H21")).Row
    mediumEndRow = Range("H:H").Find(what:="Medium", after:=Range("H21"), searchdirection:=xlPrevious).Row
    MsgBox "First and Last Row for Medium Projects: " &amp; mediumStartRow &amp; mediumEndRow

    redVar = "Red Variance" 'This is the value I am looking for in the row - Red Variance
    amberVar = "Amber Variance" 'this is the value I am looking for in the row - Amber Variance
    greenVar = "Green Variance" 'This is the value I am looking for in the row - Green Variance

    redVarCount = 0
    amberVarCount = 0
    greenVarCount = 0

    For Each Row In Range("AS" &amp; mediumStartRow &amp; ":AU" &amp; mediumEndRow)
        If Row.Value2 = redVar &gt; 0 Then
            redVarCount = redVarCount + 1
            Else
            If Row.Value2 = amberVar &gt; 0 Then
                amberVarCount = amberVarCount + 1
                Else
                If Row.Value2 = greenVar &gt; 0 Then
                    greenVarCount = greenVarCount + 1
                End If
            End If
        End If
    Next

    Range("DO22").Value = redVarCount
    Range("DO23").Value = amberVarCount
    Range("DO24").Value = greenVarCount

End Sub
</code></pre>

<p>Can anyone tell me where I have gone wrong or a better way of doing this please</p>

<p>Edit 1</p>

<p>Here is an example of the data</p>

<pre><code>Medium  Green   Red Amber       Counts for medium   
medium  Green   Green   Green       Green   1
medium  Amber   Green   Green       Amber   1
large   Green   Amber   Amber       Red 1
large   Red Green   Green           
small   Amber   Amber   Amber           
small   Green   Green   Green           
small   Green   Green   Amber           
small   Green   Amber   Green   
</code></pre>

<p>Once I can get it working for the mediums I will be doing it for the Large and Small.</p>