Q: Excel VBA Avoiding duplicate values in different columns when using Random Numbers I've put together a workbook which allows the user to input the names of a number of teams, together with their associated judges (2 per team), which will create a randomised list of teams and judges for a number of rounds.
The problem I am having is that I want to avoid any judge judging their own team.
Rather than explain the entire workbook here, I've created a simple version which should do the same thing.  The subroutines are as follows: generateRandNum, which generates a list of random numbers without duplicates in cells A1:A5 - I then use the VLOOKUP function to assign the relevant Team names associated with each number in cells B1:B5.
Public Sub generateRandNum()

lowerbound = 1
upperbound = 5
Set randomrange = Range("A1:A5")

randomrange.Clear
For Each rng1 In randomrange
    counter = counter + 1
Next

If counter > upperbound - lowerbound + 1 Then
    MsgBox ("Number of cells > number of unique random numbers")
    Exit Sub
End If

For Each Rng In randomrange
    randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1
        randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Loop
    Rng.Value = randnum
Next
End Sub

generateRandJudge generates another random number list without duplicates in cells S11:S20 beside a list of 10 names in cells T11:T20. 
Public Sub generateRandJudge()

lowerbound = 1
upperbound = 10
Set randomrange = Range("s11:s20")

randomrange.Clear
For Each rng1 In randomrange
    counter = counter + 1
Next

If counter > upperbound - lowerbound + 1 Then
    MsgBox ("Number of cells > number of unique random numbers")
    Exit Sub
End If

For Each Rng In randomrange
    randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Do While Application.WorksheetFunction.CountIf(randomrange, randnum) >= 1
        randnum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
    Loop
    Rng.Value = randnum
Next
End Sub

Using VLOOKUP again I take the randomised list of names and place them in pairs in cells (Odd) D1:D5 and (Even) F1:F5, with the teams associated to each judge in cells E1:E5 and G1:G5 respectively.
Since I want to avoid someone judging their own team, I included the following IF function in Cells H1:H5  
=IF(OR(E1=B1,G1=B1),1,0)
I then created another sub so that the user only needs to press a button for the random list to be generated:
Sub Main()
    Call generateRandNum
    Call generateRandJudge
'Check Judge values against Team values to avoid duplicates

    Dim i As Long
    For i = 1 To Rows.Count
    Next i
    If Cells(i, 8).Value = 1 Then
    Call generateRandNum
    End If

End Sub

The first part of the sub Main() works fine, but the last part does not, with an error coming up for the line:
    If Cells(i, 8).Value = 1 Then

What I wanted it to do was loop through the values in cells H1:H5 and if any were equal to 1, then it would generate another set of random team numbers until there were no duplicates and it would stop.
I'm hoping someone out there has a much more refined solution than this.  Can anyone please help?

A: I suspect this is what you want. 
Sub Main()

    Dim i As Long
    Dim sht As Excel.Worksheet
    Dim rng As Range

    'Call generateRandNum
    'Call generateRandJudge

    Set sht = ThisWorkbook.Sheets("Test") ' Change worksheet name to real name in production

    Set rng = sht.Range("H1:H10") ' change parameters if required, or use a named range instead

    'Check Judge values against Team values to avoid duplicates
    With sht
        For i = 1 To rng.Rows.Count
            If .Cells(i, 8).Value = 1 Then Debug.Print "match at row: " & i 'Call generateRandNum
        Next i
    End With
End Sub

Please notice I've commented out your functions calls and do consider using a named range instead of a fixed one for more flexibility. If you do, you can augment the number of teams and the sub will still work properly. Also notice, that as a general rule, you should always use fully qualified references as I'm doing here.
