CODEWITHSUNDEEP
vbaexcel
jgrad
jgrad

Reputation: 1

VBA Assign variable value to cells

So I am trying to use VBA in Excel to auto-generate a worksheet based on some inputs.

My problem is that when I try to assign a variable to a cell with VBA, nothing happens.

This test code worked:

Worksheets(sheetName).Cells(1, 1).Value = 1

This code (the code I care about) doesn't:

Const ROW_START As Integer = 3

For row = ROW_START To (ROW_START + 10000) Step 1
    Worksheets(sheetName).Cells(row, RAM_COL).Value = Rnd()
Next

This also worked, although it only used Rnd() once (which I should have expected):

Worksheets(sheetName).Range("A1:A10000") = Rnd()

I have numerous such loops, none of which work. I also try to assign strings to cell values in a while, which also does not work.

The worksheet referenced by sheetName is created without any problems.

Thanks for any help you can provide

Upvotes: 0

Views: 9186

Answers (1)

user4039065
user4039065

Reputation:

Beyond the undeclared or unassigned variables, bulk loading a formula and reverting to the result values will significantly speed up the operation.

Dim rw As Long, row_start As Long, row_end As Long, ram_col As Long, sheet_name As String
row_start = 3
row_end = row_start + 10000
ram_col = 1
sheet_name = "Sheet2"

With Worksheets(sheet_name)
    'this should take less than ½ second
    With .Range(.Cells(row_start, ram_col), .Cells(row_end, ram_col))
        .Clear
        .Formula = "=rand()"
        .Value = .Value2
    End With

    'this takes 3-4 seconds
    For rw = row_start To row_end Step 1
        .Cells(rw, ram_col).Value = Rnd()
    Next

End With

Upvotes: 2

Related Questions