I want to set every eighth cell on one worksheet to the value of a cell in another worksheet. I wrote this here:
For i = 5 To 45 Step 8
Set ThisWorkbook.Sheets("Sheet5").Cells(i, 3).Value = ThisWorkbook.Sheets("Sheet7").Cells(13, 31).Value
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?
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.
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
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).
This option of setting the same value for several cells was not yet mentioned:
ActiveSheet.Range("A1,B2,C3:D4").Value = "SomeValue"