Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

2 Questions (Concantenate and Returning Ranges)

Posted by Tim on January 04, 2002 1:21 PM

1). I need to concantenate a range from a series of numbers. So, I want to convert

RowStart = 1
ColumnStart = 3
RowEnd = 6
ColumnEnd = 6

to "C1:G6".

Is there a function that will give the letter part of the string without having to write a giant If/Then?

2. It appears that I cannot return a range, determined in a procedure, to a function. Does this sound correct?

Thanks,
Tim.

Check out our Excel Resources

Question1...Re: 2 Questions (Concantenate and Returning Ranges)

Posted by Aladin Akyurek on January 04, 2002 1:25 PM

=ADDRESS(1,3)&":"&ADDRESS(6,6)

Aladin

=========

Sure

Posted by Adam S. on January 04, 2002 1:32 PM

Say you have
RowStart,ColumnStart,RowEnd,ColumnEnd in A1:D1

with A2:D2 holding their values (1,3,6,6)

use (to return C1:G6)

=Address(B2,A2,4)&":"&Address(D2,C2,4)

or you could (to return $C$1:$G$6)

=Address(B2,A2)&":"&Address(D2,C2)

Hope that helps
Adam S.

Re: 2 Questions (Concantenate and Returning Ranges)

Posted by IML on January 04, 2002 2:06 PM

If you just want to get the letter of address, you could use
=LEFT(ADDRESS(ROW(A1),COLUMN(A1),2),1+(COLUMN(A1)>26))
or
=SUBSTITUTE(ADDRESS(ROW(A1),COLUMN(A1),4),ROW(A1),"")

but I think you'd be better served by what was already offered here.

As to your second question, use the indirect function.

good luck.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.