For Loop statement VBA Excel 2016

Question

I need some assistance with creating a loop statement that will determine the range start and end where a particular criteria is met.

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.

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.

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.

Sub Test3()
    Dim x As Integer
    Dim nName As String
    Sheets("BalanceSheet").Select
    nName = Range("qryDifference[[Validate Adjustment]]").Select
    Debug.PrintnName
    ' Set numrows = number of rows of data.
    NumRows = Range(nName, Range(nName).End(xlDown)).Rows.Count
    ' Select cell a1.
    ' Establish "For" loop to loop "numrows" number of times.
    For x = 1 To NumRows
        ' Insert your code here.
        MsgBox"Value found in cell " & ActiveCell.Address
        ' Selects cell down 1 row from active cell.
        ActiveCell.Offset(1, 0).Select
    Next
End Sub

This is what I have so far - this is giving me and issue with

ActiveCell.Offset(4, 0).Select
nAgentNo = Range("qryDifference[[agtno]]").Value
nValidate = Range("ryDifference[[Difference]]").Value
Debug.Print nAgentNo
Debug.Print nValidate

Type mismatch error on the above.

Sub Revised_AgentAmount()
    Dim myRange As Range
    Dim i As Long, j As Long
    Dim nAgentNo As String
    Dim nValidate As Long

    Sheets("BalanceSheet").Select

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

Darrell H · Accepted Answer · 2016-06-23 19:53:25Z

0

In your first statement you declare nName as a String then try to select it. You would need to declare it as a Range if you are going to use it as a Range object.

answered Jun 23, 2016 at 19:53

Darrell H

1,8961 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Karen Schaefer Over a year ago

Please look at my latest revision to my post.

Karen Schaefer · Accepted Answer · 2016-06-23 20:10:17Z

0

I found solution elsewhere with a if statement instead of the for loop.

=IF([@agtno]=B24,[@[agt_amt]],SUMPRODUCT((Balance!$B$2:$B$7=[@agtno])*(Balance!$F$2:$F$7="Y")*Balance!$E$2:$E$7)+[@[agt_amt]])

answered Jun 23, 2016 at 20:10

Karen Schaefer

1092 gold badges3 silver badges9 bronze badges

Collectives™ on Stack Overflow

For Loop statement VBA Excel 2016

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related