Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a Function?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a Function?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a Function?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a Function?
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. -- Regards, Tom Ogilvy "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a Function?
Thanks Tom. You guys are great!!!!
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do you Change the text/value of Range in a 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Text Function change output text color? | Excel Discussion (Misc queries) | |||
Change text colour if range of values all zero | Excel Worksheet Functions | |||
Using text for the range in AVERAGE function | Excel Worksheet Functions | |||
Is there a function to change a number to text? | Excel Worksheet Functions | |||
Function to change text to formula | Excel Programming |