Excel VBA set multiple cells to the same value

Question

I want to set every eighth cell on one worksheet to the value of a cell in another worksheet. I wrote this here:

Sub xx()
    For i = 5 To 45 Step 8
        Set ThisWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ThisWorkbook.Sheets("Sheet7").Cells(13, 31).Value
    Next i
End Sub

If I try to run it, it gives me a subscript out of range error. Sheet5 has also been named Binomial Sheet, do I have to reference this differently? Are there other ways to accomplish this?

Answer 1

This option of setting the same value for several cells was not yet mentioned:

ActiveSheet.Range("A1,B2,C3:D4").Value = "SomeValue"

Answer 2

If you are just trying to set the value of one cell to another in this way, you don't need to use the Set property - which is for objects not values.

Also ThisWorkbook refers to where the macro is held, but is not suited to calling in this way.

As such, I'd give: ActiveWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ActiveWorkbook.Sheets("Sheet7").Cells(13, 31).Value

a go, as this works fine for me.

Answer 3

The issue seemed to be that you had an end if without a corresponding If to start, and to assign a value for the range in sheet 5 you don't need to state set you can just assign the values directly like so:

Sub xx()
For i = 5 To 45 Step 8
ActiveWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ActiveWorkbook.Sheets("Sheet7").Cells(13, 31).Value
Next i
End Sub

Answer 4

Worksheets have a .Name property and a CodeName property. Generally speaking, when the worksheets are first created, the two are visually the same but referenced differently. The worksheet's .Name can be changed; the .CodeName cannot be changed.

If Sheet5 was renamed Binomial Sheet but Sheet7 was not renamed, this is the difference in referencing them.

dim i as long
with activeworkbook
    for i = 5 To 45 step 8
        'using the worksheet .Name with the names as quoted strings
        .Sheets("Binomial Sheet").Cells(i, 3) = .Sheets("Sheet7").Cells(13, 31).Value
        'using the worksheet .CodeName directly
        Sheet5.Cells(i, 3) = Sheet7.Cells(13, 31).Value
    next i
end with

The .CodeName for Sheet5 did not change when its .Name was changed to Binomial Sheet. The.CodeNames for each worksheet (and its .Name in brackets) are listed within the VBE's Project Explorer (Ctrl+R).