[Solved] do while loop in excel vba error

by


While Den Temple is correct, and you really should Dim variables independently, the real problem here is with the logic of:

For clm = 1 To 5
  ClmTtl = 0

   For copyRow = 1 To percRows
       ClmTtl = ClmTtl + Sheets(1).Cells(MyRows(copyRow), clm).Value
   Next

Next

This clears ClmTtl each time, without doing anything with the total you’ve just calculated. Thus you’re only checking the final column that you have picked. You’ll need to add in a check within the loop which gets triggered if any of the totals is not 3, and have the While loop based on that.

You are also not clearing MyRows each time you go through the do loop, so if it fails the first time, it will fail every time.

Your loop might be better as something like:

    Dim claimTotalCheck As Boolean
    claimTotalCheck = True
    Do While claimTotalCheck
        ReDim MyRows(percRows)
        For nxtRow = 1 To percRows
getNew:
        'Generate Random number
            nxtRnd = Int((numRows) * Rnd + 1)

        'Loop through array, checking for Duplicates
            For chkrnd = 1 To nxtRow
        'Get new number if Duplicate is found
                If MyRows(chkrnd) = nxtRnd Then GoTo getNew
            Next
        'Add element if Random number is unique
            MyRows(nxtRow) = nxtRnd
        Next

        claimTotalCheck = False
        For clm = 1 To 5
           ClmTtl = 0

            For copyRow = 1 To percRows
                ClmTtl = ClmTtl + Sheets(1).Cells(MyRows(copyRow), clm).Value
            Next

            If ClmTtl <> 3 Then
                claimTotalCheck = True
            End If
        Next
    Loop

1

solved do while loop in excel vba error