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