Excel crashes after running VBA script

[Razor147]

The following VBA script runs okay but when I click in a cell afterwards excel does not respond and I have to use <CTRL+ALT+DEL> to close excel. Can anybody help me please, I am new to VBA code. I am using Excel 2010. Sub Random_Name()
Dim rNum, gName, DupYN
Dim rng2 As Range
Dim rng As Range
Set rng = Range("B:B")
Set rng2 = Range("C2:C81")
rng2 = ""
For Each cell In rng
cell.Select
here:
rNum = Int((80 - 1 + 1) * Rnd + 1)
gName = Application.VLookup(rNum, Range("A2:B148"), 2, False)
DupYN = Application.WorksheetFunction. _
CountIf(Range("C2:C81"), gName)
If DupYN = 1 Or ActiveCell.Value = gName Then
GoTo here
Else
ActiveCell.Offset(0, 1).Value = gName
End If
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

What's in A2:A148? Why are you using the entire column B?

 

[Razor147]

In Column A I have rows numbered 1-148, In column B I have a list of names in rows 1-148.

 

[Andrew Poulsom]

Does this work for you?

Sub Random_Name()
    Dim rNum, gName, DupYN
    Dim rng2 As Range
    Dim rng As Range
    Dim cell As Range
    Set rng = Range("B2:B81")
    Set rng2 = Range("C2:C81")
    rng2 = ""
    For Each cell In rng
        cell.Select
here:
        rNum = Int((80 - 1 + 1) * Rnd + 1)
        gName = Application.VLookup(rNum, Range("A2:B148"), 2, False)
        DupYN = Application.WorksheetFunction. _
        CountIf(Range("C2:C81"), gName)
        If DupYN = 1 Or ActiveCell.Value = gName Then
            GoTo here
        Else
            ActiveCell.Offset(0, 1).Value = gName
        End If
    Next cell
 End Sub

 

[Andrew Poulsom]

Sorry, skip that code and try:

Sub Random_Name()
    Dim Rng As Range
    Dim Cell As Range
    Dim rNum, gName, DupYN
    Set Rng = Range("C2:C81")
    Rng.ClearContents
    For Each Cell In Rng
here:
        rNum = Int(148 * Rnd + 1)
        gName = Application.VLookup(rNum, Range("A2:B148"), 2, False)
        DupYN = Application.WorksheetFunction. _
            CountIf(Rng, gName)
        If DupYN = 1 Or Cell.Value = gName Then
            GoTo here
        Else
            Cell.Value = gName
        End If
    Next Cell
 End Sub

 

[Rick Rothstein]

The following VBA script runs okay but when I click in a cell afterwards excel does not respond and I have to use <CTRL+ALT+DEL>to close excel. Can anybody help me please, I am new to VBA code. I am using Excel 2010. Sub Random_Name()
Dim rNum, gName, DupYN
Dim rng2 As Range
Dim rng As Range
Set rng = Range("B:B")
Set rng2 = Range("C2:C81")
rng2 = ""
For Each cell In rng
cell.Select
here:
rNum = Int((80 - 1 + 1) * Rnd + 1)
gName = Application.VLookup(rNum, Range("A2:B148"), 2, False)
DupYN = Application.WorksheetFunction. _
CountIf(Range("C2:C81"), gName)
If DupYN = 1 Or ActiveCell.Value = gName Then
GoTo here
Else
ActiveCell.Offset(0, 1).Value = gName
End If
End Sub

Excel would eventually respond to you if you waited long enough. The problem with your code is in the line I highlighted in red... the rng variable was set to the entire column B (see line I highlighted in purple) and that column has over 1000000 cells in it, so your For..Next loop will iterate over 1000000 times executing the code in that loop on each iteration. A million plus iterations takes time to execute... you just didn't wait long enough. Andrew has given you code which may be what you actually want to do so you should follow up with him... I just wanted to give you an explanation for what you described was happening.

 

[Andrew Poulsom]

Rick,

I think there would be a never ending loop when all 147 names had been used.

 

[Razor147]

Thanks Andrew. I pasted your code into my spreadsheet and ran it, I am now getting the following error - Run-time error '13' Type mismatch

 

[Razor147]

Thanks Rick - I am now getting the following - error - Run-time error '13' Type mismatch

 

[Andrew Poulsom]

Thanks Andrew. I pasted your code into my spreadsheet and ran it, I am now getting the following error - Run-time error '13' Type mismatch

Which line is causing that error? The code works fine for me.