Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Example: Generating a Range String from numbers.
This is a coding example of how to make a range string from numbers.
Function RangeString(Row1, Col1, Row2, Col2) ' Return Range String from Arguments. ' No checking for reversed range specs. RangeString = Nr2Col(Col1) & Row1 & ":" & Nr2Col(Col2) & Row2 End Function Function Nr2Col(NR) ' Turn NR into Excel Column Designator. If NR <= 26 Then Nr2Col = Chr(64 + NR) Else Nr1 = Chr(64 + Int(NR / 26)) Nr2 = Chr(65 + NR Mod 26) Nr2Col = Nr1 & Nr2 End If End Function RangeString(1,1,2,5) returns "A1:E2". Let me know if you find this helpful. Thanks Mac@ SV-CaliforniaGirl.Com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Example: Generating a Range String from numbers.
While your logic and coding are fine, you may find using a built-in
method more efficient: Public Function RangeString( _ Row1 As Long, Col1 As Long, _ Row2 As Long, Col2 As Long) As Variant On Error GoTo ErrHandler RangeString = Range(Cells(Row1, Col1), _ Cells(Row2, Col2)).Address(False, False) Exit Function ErrHandler: RangeString = CVErr(xlErrValue) End Function In article . net, "Mac Lingo" wrote: This is a coding example of how to make a range string from numbers. Function RangeString(Row1, Col1, Row2, Col2) ' Return Range String from Arguments. ' No checking for reversed range specs. RangeString = Nr2Col(Col1) & Row1 & ":" & Nr2Col(Col2) & Row2 End Function Function Nr2Col(NR) ' Turn NR into Excel Column Designator. If NR <= 26 Then Nr2Col = Chr(64 + NR) Else Nr1 = Chr(64 + Int(NR / 26)) Nr2 = Chr(65 + NR Mod 26) Nr2Col = Nr1 & Nr2 End If End Function RangeString(1,1,2,5) returns "A1:E2". Let me know if you find this helpful. Thanks Mac@ SV-CaliforniaGirl.Com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Example: Generating a Range String from numbers.
Thanks, JE McGimpsey,
Much more elegant solution, and it handles the incorrect specification problem as well. But what is the "Address(False,False)" for? Mac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Example: Generating a Range String from numbers.
In your code module, highlight address and hit F1.
-- Regards, Tom Ogilvy "Mac Lingo" wrote in message link.net... Thanks, JE McGimpsey, Much more elegant solution, and it handles the incorrect specification problem as well. But what is the "Address(False,False)" for? Mac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RANDBETWEEN generating numbers outside range | Excel Worksheet Functions | |||
Generating random numbers | Excel Worksheet Functions | |||
Generating a random 17-character alphanumeric string | Excel Discussion (Misc queries) | |||
create self-generating numbers with letters and numbers | Excel Discussion (Misc queries) | |||
Convert String of 512 numbers to a range | Excel Programming |