Generate test data from range of values using vba

[karthik82.vk]

Hi,

I am generating random values from a pre specified range using VBA code. I have the pre-specified Items placed in Sheet 1 and the test data will gets generated in sheet 3 which is the output sheet. Below is the VBA Code that I use for generating test data.

Sub Generate_Product()
Dim myRange As Range
Dim n As Integer
Dim fData As Variant
Dim sData As Variant
Dim iC As Byte
Dim mRand As Byte
sData = Sheet3.Range("F2:G11")

Sheets("Output").Activate

n = Application.InputBox(Prompt:="Please enter total values to generate", Title:="Total Values Count", Type:=1)

ReDim fData(1 To n, 1 To 2)

Set myRange = Application.InputBox(Prompt:="Please Select a Range for inputing date values", Title:="Test Dates Generation", Type:=8)

If myRange Is Nothing Then
Exit Sub
Else
myRange.Select
End If

For iC = 1 To n
mRand = Int(Rnd() * 10) + 1
fData(iC, 1) = sData(mRand, 1)
fData(iC, 2) = sData(mRand, 2)
'iC.Value = fData
Next
myRange.Cells = fData
End Sub

I am facing two problems in this code.

1. The first problem is when I change the sData = Sheet3.Range("F2:G11") range to anyother value say for example G11 to G6, I am getting the error as Subscription Out of Range. I dont know why this is occurring.
2. Secondly I am not able to generate values more than 250. If enter values more than 250 I am getting the error Runtime Error 6. Over flow.

Can any one help me with this. Below is my image of sheet3

 

[Andrew Poulsom]

In this line:

mRand = Int(Rnd() * 10) + 1

10 is the number of rows in sData. Adjust to suit - you can use UBound(sData, 1).

Also try declaring iC and mRand as Long rather than Byte.