Excel VBA: Iterating over range parameter and change cell values

Question

I believe what I am trying to do is pretty simple. I want to iterate over a Range parameter and change the value for each cell in that range.

Function test(thisRange As Range)
    For Each c In thisRange.Cells
         c.Value = 1
    Next
End Function

The above is a simple example of what I want to do, but doesn't seem to work. When I debug this, Excel seems to be throwing an error when it hits c.Value = 1. Why does this not work?

Answer 1

This works for me

Option Explicit

Sub Sample()
    Dim ret
    ret = test(Sheets("Sheet1").Range("A1:A15"))
End Sub

Function test(thisRange As Range)
    Dim c As Range
    For Each c In thisRange.Cells
         c.Value = 1
    Next
End Function

BTW we don't need to use a Function. A function is used to return a value. Try this

Option Explicit

Sub Sample()
    test Sheets("Sheet1").Range("A1:A15")
End Sub

Sub test(thisRange As Range)
    Dim c As Range
    For Each c In thisRange.Cells
         c.Value = 1
    Next
End Sub