Dave,

Are you trying to run this as a worksheet function? If so, you can only
return a value, not change values in other cells.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave" wrote in message
...
Is it possible to change the values of a range within a function? For
example:

This Works...

Sub MyFunction()
Dim i As Integer
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A5")
Set rng2 = Range("G1:G5")
rng1.Select
rng2.Select
rng2.Clear

For i = rng1.Row To rng1.Rows.Count
rng2.Cells(i, 1) = rng1.Cells(i, 1)
rng2.Cells(i, 2) = rng1.Cells(i, 2)
Next
End Sub

But this doesn't...
Function MyFunction()
Dim i As Integer
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:A5")
Set rng2 = Range("G1:G5")
rng1.Select
rng2.Select
rng2.Clear

For i = rng1.Row To rng1.Rows.Count
rng2.Cells(i, 1) = rng1.Cells(i, 1)
rng2.Cells(i, 2) = rng1.Cells(i, 2)
Next
End Function

The function will have an error at the statement:
rng2.Cells(i, 1) = rng1.Cells(i, 1)

Can someone show me how to accomplish this?

TIA