CODEWITHSUNDEEP
excelexcel-2016vba
Karen Schaefer
Karen Schaefer

Reputation: 107

For Loop statement VBA Excel 2016

Proper syntax Match and If not isblank

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

Upvotes: 0

Views: 225

Answers (2)

Karen Schaefer
Karen Schaefer

Reputation: 107

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]])

Upvotes: 0

Darrell H
Darrell H

Reputation: 1886

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.

Upvotes: 0

Related Questions