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?

Concurrence

Remove Set and make sure the sheet names are OK. – Gisellegish

If the name of the sheet (not VBA object) is Binomial Sheet then you have to use ThisWorkbook.Sheets("Binomial Sheet").Cells(... or Sheet5.Cells(... – Gisellegish

Answer 1

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

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

Diplomacy

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.

Regine

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

Gessner

A sentence or two of explanation would have gone a long way toward making this a much better answer. – Tidwell

A follow up question: It writes the value now, but it doesn't write the same value in each cell. Sometimes it writes 0 and sometimes 1 or 2, even though the cell from sheet7 reads 0 – Concurrence

@Concurrence - tested this using a value in cell "AE13" in sheet7 and it worked for me – Gessner

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).

Tidwell

Nice explanation, but the .CodeName cannot be changed is not true. You can change it in the properties window. – Gisellegish

That was supposed to be a secret. ;) – Tidwell

@Jeeped, Good explanation. Do you ever personally use the Sheet object as opposed to going through Sheets(...)? I always thought the default names were awful in terms of descriptions... and you've got that issue about easily changing the name and breaking all your object references. – Parbuckle

@Byron - Typically, I use worksheet names but that is the nature of the work I do. To a lesser extent I will use codenames and sometimes (rarely) index numbers. The latter when I specifically do not a finite worksheet queue reordered. It really depends on the situation. For demonstration purposes here, codenames are confusing to the beginner and I prefer to not have to explain every With/End With snippet. I already get enough of that with my use of 1E+99. – Tidwell