CODEWITHSUNDEEP
vb.netexcel
Nonagon
Nonagon

Reputation: 407

Adding different randomly generated numbers in an Excel sheet with VB.NET

So i have an excel sheet which i am adding data to. I have made headers 

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim xlRange As Excel.Range
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim rNum As Random = New Random

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlRange = xlWorkSheet.UsedRange

        With xlWorkSheet
            .Range("A1").Value = "col1"
            .Range("B1").Value = "col2"
            .Range("C1").Value = "col3"
            .Range("D1").Value = "col4"
            .Range("E1").Value = "col5"
            .Range("F1").Value = "col6"
        End With

what i would like to do for col5 is have every row and individual, randomly generated number using rNum to do it. So far i have been able to populate multiple rows in the column but it is the same number and not unique

Any advice on this?

EDIT

The only code i had was this:

xlWorkSheet.Range("E2:E10").Value = rNum.Next()

Upvotes: 0

Views: 28

Answers (2)

ChicagoMike
ChicagoMike

Reputation: 618

Instead of using

xlWorkSheet.Range("E2:E10").Value = rNum.Next()

Try using

Dim rCell As Range
Dim rRng As Range

Set rRng = xlWorkSheet.Range("E2:E10")

For Each rCell In rRng.Cells
    rCell.Value = rNum.Next()
Next rCell

Does that do the trick?

Upvotes: 0

tezzo
tezzo

Reputation: 11105

You can use this code to set a different value for every cell:

For i As Integer = 2 To 10
    xlWorkSheet.Range("E" & i).Value = rNum.Next()
Next i

Sincerely, Doom Guy with 100% health

Upvotes: 1

Related Questions