CODEWITHSUNDEEP
excelvba
L michael
L michael

Reputation: 21

Cannot change the cells value

I want to change rows 2 to 10 in column 4 to 10 but it does not work.

Restriction: d As Single must not be changed

Sub ua()
    Dim d As Single
    Dim i As Integer

    d = Cells(i, 4)

    For i = 2 To 10
        d = 10
    Next i
End Sub

Upvotes: 1

Views: 434

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Change Sheet name and try:

Sub test()

    With Sheet1

        .Range(.Cells(2, 4), .Cells(10, 10)).Value = 10

    End With

End Sub

Upvotes: 2

Vityata
Vityata

Reputation: 43575

Looping through the rows and columns is one option:

Sub TestMe()

    Dim myRow As Long, myCol As Long
    Dim d As Single: d = 10
    For myRow = 2 To 10
        For myCol = 4 To 10
            Worksheets(1).Cells(myRow, myCol) = d
        Next myCol
    Next myRow

End Sub

Another one is using the single line solution of @Jeep from the comments - 

With Workheets(1)
    .Range(.Cells(2, 4), .Cells(10, 10)) = 10
end with

Upvotes: 4

Strawberryshrub
Strawberryshrub

Reputation: 3379

You actually doing nothing with your code (you just set a variable and then you loop without any restult on cell values). If you want to change a value in a cell you have to write this in your code. You can change a value in a cell like this:

 Cells(3, 2).Value = 2

If you want this variable and in a loop you can write the following:

For i = 2 to 10
  Cells(3, i).Value = 1000 'Set the value of the cell to 1000
Next i

Upvotes: 0

Related Questions