Thursday, February 23, 2017

Add Random Data in Spreadsheet

There can be various ways to add random data in spreadsheet, sometime we need high number of records to start a unit testing, but dev application not having too much of data initially. Here is the example how to add number of records in spreadsheet, i my case best way is use macro code.

To add random data in spreadsheet you can use macro code and you have to save spreadsheet "macro enable workbook" if you are using higher version of office, advantage to use this code is no more manual work. Use below code to generate data.

Public Sub ran()
Dim b As Range
    For i = 1 To 800000
        Dim r As Double
        r = Int(Timer * Rnd())
        Cells(i, 20).Value = 0
        Cells(i, 21).Value = "USD"
        Cells(i, 22).Value = ""
        Cells(i, 23).Value = "A clever fox just jump over the lazy dog"
        Cells(i, 24).Value = r
    Next i
End Sub

Dynamic data generated in "r" variable and rest of data is static.

Best way to run macro
1. Save spreadsheet in Macro enable workbook
2. Record the Macro
3. Stop the Macro
4. Then go to Macro > and Edit
5. Copy and paste above code in popup window
6. and run the macro code

No comments:

Post a Comment

web stats