当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel VBA set multiple cells to the same value

问题描述:

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?

网友答案:

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"
分享给朋友:
您可能感兴趣的文章:
随机阅读: