Generate test data from range of values using vba

[karthik82vk]

Hi,

I am new to excel vba and i am working on a request. I want to randomize a range of values stored in sheet 1 and insert them in another cell.

I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50

the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba

Untitled.png

Any help is highly appreciable.

Regards,
KV

[tom.hogan]

Assuming the header data for the two tables starts in A1 and E1 respectively (i.e. as in the picture the actual data starts in A3 and E3), this will work:

Option Explicit
Option Base 1
Sub GenerateTestData()

Dim vSourceDataArray As Variant
Dim vRandProdsArray(1 To 50, 2) As Variant
Dim iMaxProdsArray(1 To 10) As Integer
Dim rTestData As Range
Dim n As Integer
Dim m As Integer

vSourceDataArray = Range("E3:G13").Value 'Transfer data to array

For n = 1 To 50 'Fill array based on random number generator
m = Int(10 * Rnd + 1) 'Generate random number 1 to 10
iMaxProdsArray(m) = iMaxProdsArray(m) + 1
If iMaxProdsArray(m) > 5 Then 'If random number used 5 times or more reset n and try again
  n = n - 1
Else 'Fill array with product values
  vRandProdsArray(n, 1) = vSourceDataArray(m, 2)
  vRandProdsArray(n, 2) = vSourceDataArray(m, 3)
End If
Next n

Set rTestData = Range("a3:b52") 'Copy random products array to spreadsheet
rTestData = vRandProdsArray

End Sub

Tom

[karthik82vk]

Hi,

Thanks for the code and helping me.

-KV