How Do you Change the text/value of Range in a Function?

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

Are you trying to run this as a worksheet function? If so, you can only

return a value, not change values in other cells.

Yes I am. That's what I was afraid of. I was looking at your reply to
creating a user defined array function. Can a worksheet function return an
array of values to fill a range in the fashion I was attempting to do? The
idea is to allow my users to enter in a range of manufacturing lots, and
return information for those lots. The user selects the range, the worksheet
function responds with 1..n values.

I really appreciate your help!

Dave

 

[Tom Ogilvy]

if you want to do

=MyFunction()

then no, a function used in a worksheet can not change anything except the
value it returns to the cell in which it is located.

 

[Guest]

Thanks Tom. You guys are great!!!!

 

[Myrna Larson]

Can a worksheet function return an

array of values to fill a range in the fashion I was attempting to do? The
idea is to allow my users to enter in a range of manufacturing lots, and
return information for those lots. The user selects the range, the worksheet
function responds with 1..n values.

Yes, it can return an array of values. The entire range, with the number of
cells equal to the number of returned values, must be selected when you enter
the formula, just as you do with an array formula using built-in functions.