For Loop statement VBA Excel 2016
<p><a href="https://stackoverflow.com/questions/37979585/proper-syntax-match-and-if-not-isblank">Proper syntax Match and If not isblank</a></p>
<p>I need some assistance with creating a loop statement that will determine the range start and end where a particular criteria is met.</p>
<p>I found these statements on the web and need help to modify them to loop thru two different worksheets to update a value on 1 of the worksheets.</p>
<p>This one has an issue returning True or False value for the Range when I want to pass the actual named range for look up where this field = Y, then returns the value from another column.  I original tried using Match and If is not blank function.  But that is very limiting.</p>
<p>See the previous post to see what I am trying to accomplish - I know I will need to expand the code samples and probably will need help with this modification.</p>
<pre class="lang-vb prettyprint-override"><code>Sub Test3()
    Dim x As Integer
    Dim nName As String
    Sheets(&quot;BalanceSheet&quot;).Select
    nName = Range(&quot;qryDifference[[Validate Adjustment]]&quot;).Select
    Debug.PrintnName
    ' Set numrows = number of rows of data.
    NumRows = Range(nName, Range(nName).End(xlDown)).Rows.Count
    ' Select cell a1.
    ' Establish &quot;For&quot; loop to loop &quot;numrows&quot; number of times.
    For x = 1 To NumRows
        ' Insert your code here.
        MsgBox&quot;Value found in cell &quot; &amp; ActiveCell.Address
        ' Selects cell down 1 row from active cell.
        ActiveCell.Offset(1, 0).Select
    Next
End Sub
</code></pre>
<p>This is what I have so far - this is giving me and issue with</p>
<pre class="lang-vb prettyprint-override"><code>ActiveCell.Offset(4, 0).Select
nAgentNo = Range(&quot;qryDifference[[agtno]]&quot;).Value
nValidate = Range(&quot;ryDifference[[Difference]]&quot;).Value
Debug.Print nAgentNo
Debug.Print nValidate
</code></pre>
<p>Type mismatch error on the above.</p>
<pre class="lang-vb prettyprint-override"><code>Sub Revised_AgentAmount()
    Dim myRange As Range
    Dim i As Long, j As Long
    Dim nAgentNo As String
    Dim nValidate As Long

    Sheets(&quot;BalanceSheet&quot;).Select

    Set myRange = Range(&quot;qryDifference[[Validate Adjustment]]&quot;)
    For i = 1 To myRange.Rows.Count
        For j = 1 To myRange.Columns.Count
            If myRange(i, j).Value = &quot;Y&quot; Then
                ActiveCell.Offset(4, 0).Select
                nAgentNo = Range(&quot;qryDifference[[agtno]]&quot;).Value
                nValidate = Range(&quot;ryDifference[[Difference]]&quot;).Value
                Debug.Print nAgentNo
                Debug.Print nValidate
            End If
        Next j
    Next i
End Sub
</code></pre>