CODEWITHSUNDEEP
excelvba
Kai Mehta
Kai Mehta

Reputation: 35

Add numbers to each column consecutively

I am relatively new to VBA and I am working on the following exercise from WiseOwl in order to improve my coding skills. The goal of the task is to populate random numbers within cells B3:G3 randomly, where no random number can be the same. In trying to accomplish that, I have done the following macro

Sub LuckyDip()
Dim i As Variant
Dim Num As Integer



For Each i In Worksheets(1).Range("B3:G3")
   Num = WorksheetFunction.RandBetween(1, 59)
   Cells(3, i).Offset(0, 1).Value = Num
   
Next i
End Sub

The problem with the code is the following is that instead of populating random numbers within each cell consecutively within the range of B3:G3, it instead populates the random numbers by the random number generated within those cells in the range. For example, if in the cell B3, the random number is 30, then the program will populate a random number in row 3 and cell 30, rather than actually replacing the random number 30 which was originally in cell B3. The problem I know lies in my For Each loop, however, I am not sure how to change it accordingly where the random number does not refer to a specific cell at all, and rather just acts as a value that I can replace. Thanks for any assistance in advance.

Upvotes: 1

Views: 58

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

You should learn right off the bat that reading/writing cells, one-by-one is a terrible idea. It's really, really slow. And while it won't be noticeable in the tiny range in this question, it becomes painful with larger ranges. So just get into the habit of reading and writing ARRAYS to the worksheet instead of individual values.

For example:

Sub LuckyDip()
    Dim j&, v
    With Worksheets(1).Range("B3:G3")
        v = .Value2
        With WorksheetFunction
            For j = 1 To UBound(v, 2)
                v(1, j) = .RandBetween(1, 59)
            Next
        End With
        .Value2 = v     '<--v is an array of values,  written to the sheet in one go.
    End With
End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152465

With

For Each i In Worksheets(1).Range("B3:G3")

i is a range object and not a number and as such:

Cells(3, i)

will refer to the value in i to be the column number.

You want a standard for loop:

For i = 2 to 7

Where 2 is the column number of B and 7 the column number of G

Sub LuckyDip()
    Dim i As Long
    Dim Num As Long

    For i = 2 To 7
       Num = WorksheetFunction.RandBetween(1, 59)
       ActiveSheet.Cells(3, i).Offset(0, 1).Value = Num
    Next i
End Sub

One note: As you can see, I added ActiveSheet one should get in the habit of always denoting the parent sheet to all range/cell objects. Ideally using the codename.

Upvotes: 0

Related Questions