9

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?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Prot
  • 99
  • 1
  • 1
  • 6

4 Answers4

19

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

ActiveSheet.Range("A1,B2,C3:D4").Value = "SomeValue"
ZygD
  • 22,092
  • 39
  • 79
  • 102
1

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.

Trum
  • 620
  • 3
  • 12
1

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
Dibstar
  • 2,334
  • 2
  • 24
  • 38
  • A sentence or two of explanation would have gone a long way toward making this a much better answer. –  Jun 04 '15 at 08:47
  • 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 – Prot Jun 04 '15 at 08:55
  • @Prot - tested this using a value in cell "AE13" in sheet7 and it worked for me – Dibstar Jun 04 '15 at 09:01
1

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

  • 1
    Nice explanation, but _the .CodeName cannot be changed_ is not true. You can change it in the properties window. – BrakNicku Jun 04 '15 at 09:06
  • 1
    That was supposed to be a secret. ;) –  Jun 04 '15 at 09:07
  • @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. – Byron Wall Jun 04 '15 at 22:35
  • @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. –  Jun 04 '15 at 23:49