Convert Row/Column to Range

[vthokienj]

I would think this would be simple, but I have yet to find the solution. If I know the boundaries in row/column format, how do I get this as a range?

So if I know my start is row1/col1 and end is row3/col3, is there a function I can call with the params 1,1,3,3 that would return "A1:C3"?

Thanks

 

[Jerry Sullivan]

I'm not aware of a single worksheet function that would do that.
You could make a User Defined Function (UDF) to do that using VBA.

You could try:
=ADDRESS(1,1)&":"&ADDRESS(3,3)

This isn't a true range reference, but it would work in an INDIRECT formula.

 

[vthokienj]

Thanks, I will try that if the next question is not possible.

Could I instead put the range using integers in the Range reference?
My function takes a range, so the following is what currently goes on the
worksheet to reference columns 32 and 33, rows 4-53:

=myfunc($I1,$AF$4:$AG$53)

I tried to modify on the worksheet to take this form:

=myfunc($I1,Cells(31,4),Cells(32,53))

or

=myfunc($I1,Range(Cells(31,4),Cells(32,53)))

Is there simply a way to reformat this function call to use the columns for the range?

Thanks

 

[Jerry Sullivan]

Here are two ways you could leave your myfunc code unchanged and use Row and Column number values as parameters.

I don't know what your User Defined Function does, but lets use an example UDF that returns a string that includes the Value in the first Cell ($I$1)
and the Address of the second Range.

Function myfunc(rCell As Range, rRange As Range)
    myfunc = rCell.Value & "---> " & rRange.Address
End Function

Below are formulas that each work using parameters of $I1,$AF$4:$AG$53 from your example.
(The column numbers are actually 32,33 for AF,AG).

Excel Workbook
	H	I
1		Read Me
2
3	Using A1 Reference:	Read Me---> $AF$4:$AG$53
4
5	Using INDIRECT + ADDRESS:	Read Me---> $AF$4:$AG$53
6
7	Using OFFSET:	Read Me---> $AF$4:$AG$53
8
Sheet

The INDIRECT+ADDRESS is probably cleaner than the OFFSET option.

Alternatively, you could change your function declaration to take a Range and four Longs as Parameters. Just ask if you want some help doing that.

 

[vthokienj]

Thanks for this post. I5 is exactly what I was looking for.

 

[vthokienj]

One more question about this that I can not find an example of.
How can you specify cells on another worksheet?
I have tried inserting "sheetStuff!" at various points with no luck.

My formula looks like this:

=myfunc(INDIRECT(ADDRESS(2,2)), INDIRECT(ADDRESS(6,1)&":"&ADDRESS(55,2)))

Cell 2,2 is on the same sheet as the formula, but the cell range is referencng data on another sheet "sheetStuff"

Thanks again for any additional help to this.

 

[Jerry Sullivan]

The ADDRESS function has some optional parameters:
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

sheet_text allows you to specify the name of a sheet other than the one the formula is in.

Testing with an example formula, this seems to work....
=myfunc(INDIRECT(ADDRESS(2,2)), INDIRECT(ADDRESS(6,1,,,"sheetStuff")&":"&ADDRESS(55,2)))