My VB code keeps below keeps generating a run time error 13. Not sure why. Can someone point out what is wrong with the code below:
Sub GenerateUniqueData()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim firstNames() As String
Dim lastNames() As String
Dim usedNames As Object
Dim fullName As String
Set ws = ActiveSheet
Set usedNames = CreateObject("Scripting.Dictionary")
' Clear existing data
ws.Range("A2:E1001").Clear
' Add headers
ws.Cells(1, 1).Value = "Full Name"
ws.Cells(1, 2).Value = "Age"
ws.Cells(1, 3).Value = "Height (cm)"
ws.Cells(1, 4).Value = "Weight (kg)"
ws.Cells(1, 5).Value = "Bone Density"
' Define larger arrays of first names and last names
firstNames = Array("John", "Maria", "Robert", "Emily", "Michael", "Sarah", "David", "Lisa", "James", "Karen", _
"William", "Jennifer", "Richard", "Elizabeth", "Thomas", "Nancy", "Charles", "Patricia", "Daniel", "Linda", _
"Matthew", "Barbara", "Anthony", "Margaret", "Donald", "Susan", "Mark", "Dorothy", "Paul", "Jessica", _
"Steven", "Ashley", "Andrew", "Kimberly", "Kenneth", "Donna", "Joshua", "Carol", "George", "Michelle", _
"Kevin", "Amanda", "Brian", "Betty", "Edward", "Melissa", "Ronald", "Deborah", "Timothy", "Stephanie")
lastNames = Array("Smith", "Garcia", "Johnson", "Chen", "Brown", "Davis", "Wilson", "Anderson", "Taylor", "Lee", _
"White", "Harris", "Martin", "Thompson", "Moore", "Young", "Allen", "King", "Wright", "Scott", _
"Green", "Baker", "Adams", "Nelson", "Hill", "Ramirez", "Campbell", "Mitchell", "Roberts", "Carter", _
"Phillips", "Evans", "Turner", "Torres", "Parker", "Collins", "Edwards", "Stewart", "Flores", "Morris", _
"Nguyen", "Murphy", "Rivera", "Cook", "Rogers", "Morgan", "Peterson", "Cooper", "Reed", "Bailey")
' Generate 1000 rows of data
For i = 2 To 1001
' Generate unique full name
Do
fullName = firstNames(Int(Rnd() * UBound(firstNames))) & " " & lastNames(Int(Rnd() * UBound(lastNames)))
Loop While usedNames.Exists(fullName)
usedNames.Add fullName, Nothing
' Full Name
ws.Cells(i, 1).Value = fullName
' Age
ws.Cells(i, 2).Value = Application.WorksheetFunction.RandBetween(18, 80)
' Height
ws.Cells(i, 3).Value = Application.WorksheetFunction.RandBetween(150, 200)
' Weight
ws.Cells(i, 4).Value = Application.WorksheetFunction.RoundUp(Application.WorksheetFunction.Norm_Inv(Rnd(), 70, 15), 0)
' Bone Density
ws.Cells(i, 5).Value = Round(Application.WorksheetFunction.Norm_Inv(Rnd(), 1.2, 0.05), 2)
Next i
' Format headers
ws.Range("A1:E1").Font.Bold = True
MsgBox "Data generation complete!", vbInformation
End Sub
New contributor
Shakil Sheikh is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.