Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
Can anyone point me to help topics on named ranges. I
cannot find the syntax to set the rSource and rDest variables below. My puzzle has two arrays, an array of scores and an array of results seperated by several rows of blank rows. Each array has a name in the top-left cell (NS_Scores, NS_Percentages) and each array is approx 8 rows * 32 columns. Both the widh and height will be variable once I have better control of the code. I want to apply a user-defined function to populate the results array (one column at a time) from the Scores array. How do I pass references to individual columns of the two arrays to the user defined function? Public CalcPercentage (rSource as range, rDest as range) as boolean ' some logic to produce the Dest array from the Source. exit function .... dim rSource as range dim i as integer dim iNoRows as integer iNoRows = 8 for i=1 to 24 rSource = range(range("MyScores").offset(i, col), range("MyScores").offset(iNoRows, col)) rDest = range(rSource).offset("MyPercentage")) dim bResult as boolean bResult = CalcPercentage (rSource, rDest) next i .... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
BigJim wrote:
I want to apply a user-defined function to populate the results array (one column at a time) from the Scores array. A user-defined function can only alter cells by returning a value into the cell(s) from which it was called. So you could, for example, have a function that returns an array of values to the destination range and array-enter the function call into that range using Ctrl+Shift+Enter. The function might look like: Function CalcPercentage(rSource As Range) Dim rResults() Dim iRow As Integer ReDim rResults(1 To rSource.Rows.Count, 1 To 1) For iRow = 1 To rSource.Rows.Count rResults(iRow, 1) = something or other Next CalcPercentage = rResults End Function Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
Thanks. My problem is with the syntax of the
"rSource = " line below. dim iNoRows as integer dim rSrc as range iNoRows = 8 dim iColNo as integer for iColNo=1 to 24 rSrc = range(range("MyScores").offset(iColNo, 1), range("MyScores").offset(iColNo, iNoRows)) -----Original Message----- Can anyone point me to help topics on named ranges. I cannot find the syntax to set the rSource and rDest variables below. My puzzle has two arrays, an array of scores and an array of results seperated by several rows of blank rows. Each array has a name in the top-left cell (NS_Scores, NS_Percentages) and each array is approx 8 rows * 32 columns. Both the widh and height will be variable once I have better control of the code. I want to apply a user-defined function to populate the results array (one column at a time) from the Scores array. How do I pass references to individual columns of the two arrays to the user defined function? Public CalcPercentage (rSource as range, rDest as range) as boolean ' some logic to produce the Dest array from the Source. exit function .... dim rSource as range dim i as integer dim iNoRows as integer iNoRows = 8 for i=1 to 24 rSource = range(range("MyScores").offset(i, col), range("MyScores").offset(iNoRows, col)) rDest = range(rSource).offset("MyPercentage")) dim bResult as boolean bResult = CalcPercentage (rSource, rDest) next i .... . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
BigJim wrote:
Thanks. My problem is with the syntax of the "rSource = " line below. Putting Set in front of rSrc would be helpful. That's why it will be saying "Object variable not Set" to you. You always have to use Set when making an object variable refer to a particular object, as here. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - reply in newsgroup |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range naming
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Naming the range of data | Excel Discussion (Misc queries) | |||
Naming a range | Excel Discussion (Misc queries) | |||
Range naming | Excel Discussion (Misc queries) | |||
Naming a range | Excel Discussion (Misc queries) | |||
naming a range in excel | Excel Programming |