| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for expanding a range
Hi,
I have ranges of numbers which are inconsistent and random; by this i mean there is no logical order. I have separated the numbers using a dash i.e (45678 - 45688) I want excel to expand the range so that it displays 45678, 45679, 45680, etc up to 45688. The ranges are all in one cell however there is hundreds of cells all with different ranges. Some of the ranges are split up by single numbers to give an example ( 45678 - 45688; 45690; 45693 - 45699) it is important i only get the ranges separated by dashes not the entire range. I would like excel to convert the range and expand it into the same cell as the single numbers with a preference for numerical ordering Any help or assistance is greatly appreciated; i don't want to write this out by hand if possible. Ian |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for expanding a range
Try this code. the code take data from column A. Change as necessary. the
code will also stop if there are any empty cells in column A Sub test3() RowCount = 1 Do While Range("A" & RowCount) < "" If InStr(Range("A" & RowCount), "-") 0 Then CellStr = Range("A" & RowCount) NewStr = "" Do While CellStr < "" If InStr(CellStr, ";") 0 Then FirstPart = Trim(Left(CellStr, InStr(CellStr, ";") - 1)) CellStr = Trim(Mid(CellStr, InStr(CellStr, ";") + 1)) Else FirstPart = CellStr CellStr = "" End If If NewStr < "" Then NewStr = NewStr & "; " End If If InStr(FirstPart, "-") 0 Then FirstNum = Val(Trim(Left(FirstPart, InStr(FirstPart, "-") - 1))) LastNum = Val(Trim(Mid(FirstPart, InStr(FirstPart, "-") + 1))) For NumCount = FirstNum To LastNum If NumCount < FirstNum Then NewStr = NewStr & ", " & NumCount Else NewStr = NewStr & NumCount End If Next NumCount Else NewStr = NewStr & FirstPart End If Loop Range("A" & RowCount) = NewStr End If RowCount = RowCount + 1 Loop End Sub "Macca1984" wrote: Hi, I have ranges of numbers which are inconsistent and random; by this i mean there is no logical order. I have separated the numbers using a dash i.e (45678 - 45688) I want excel to expand the range so that it displays 45678, 45679, 45680, etc up to 45688. The ranges are all in one cell however there is hundreds of cells all with different ranges. Some of the ranges are split up by single numbers to give an example ( 45678 - 45688; 45690; 45693 - 45699) it is important i only get the ranges separated by dashes not the entire range. I would like excel to convert the range and expand it into the same cell as the single numbers with a preference for numerical ordering Any help or assistance is greatly appreciated; i don't want to write this out by hand if possible. Ian |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for expanding a range
Thanks for the code; i run it and recieved a
run-time error '7': Out of memory, closed all non essential programs any further suggestions? Will it affect the code if there is no range only a single number i.e 75000? "Joel" wrote: Try this code. the code take data from column A. Change as necessary. the code will also stop if there are any empty cells in column A Sub test3() RowCount = 1 Do While Range("A" & RowCount) < "" If InStr(Range("A" & RowCount), "-") 0 Then CellStr = Range("A" & RowCount) NewStr = "" Do While CellStr < "" If InStr(CellStr, ";") 0 Then FirstPart = Trim(Left(CellStr, InStr(CellStr, ";") - 1)) CellStr = Trim(Mid(CellStr, InStr(CellStr, ";") + 1)) Else FirstPart = CellStr CellStr = "" End If If NewStr < "" Then NewStr = NewStr & "; " End If If InStr(FirstPart, "-") 0 Then FirstNum = Val(Trim(Left(FirstPart, InStr(FirstPart, "-") - 1))) LastNum = Val(Trim(Mid(FirstPart, InStr(FirstPart, "-") + 1))) For NumCount = FirstNum To LastNum If NumCount < FirstNum Then NewStr = NewStr & ", " & NumCount Else NewStr = NewStr & NumCount End If Next NumCount Else NewStr = NewStr & FirstPart End If Loop Range("A" & RowCount) = NewStr End If RowCount = RowCount + 1 Loop End Sub "Macca1984" wrote: Hi, I have ranges of numbers which are inconsistent and random; by this i mean there is no logical order. I have separated the numbers using a dash i.e (45678 - 45688) I want excel to expand the range so that it displays 45678, 45679, 45680, etc up to 45688. The ranges are all in one cell however there is hundreds of cells all with different ranges. Some of the ranges are split up by single numbers to give an example ( 45678 - 45688; 45690; 45693 - 45699) it is important i only get the ranges separated by dashes not the entire range. I would like excel to convert the range and expand it into the same cell as the single numbers with a preference for numerical ordering Any help or assistance is greatly appreciated; i don't want to write this out by hand if possible. Ian |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for expanding a range
The code only changes cells where it finds a dash (see line below). I don't
know if you made any changes to the code. The code was written to move down column A on the active worksheet. If you changed the code to a differrent column make sure you change ALL these places where the code looks at column A. Run time error sounds like the code is in some loop. or it is finding a very long string of data. In excel 2003 ther is a limit of 16K of data in a single cell. If your range of numbers is very long maybe it is exceeding the number of characters in a cell. Instruction which checks for a dash in the cell. If InStr(Range("A" & RowCount), "-") 0 "Macca1984" wrote: Thanks for the code; i run it and recieved a run-time error '7': Out of memory, closed all non essential programs any further suggestions? Will it affect the code if there is no range only a single number i.e 75000? "Joel" wrote: Try this code. the code take data from column A. Change as necessary. the code will also stop if there are any empty cells in column A Sub test3() RowCount = 1 Do While Range("A" & RowCount) < "" If InStr(Range("A" & RowCount), "-") 0 Then CellStr = Range("A" & RowCount) NewStr = "" Do While CellStr < "" If InStr(CellStr, ";") 0 Then FirstPart = Trim(Left(CellStr, InStr(CellStr, ";") - 1)) CellStr = Trim(Mid(CellStr, InStr(CellStr, ";") + 1)) Else FirstPart = CellStr CellStr = "" End If If NewStr < "" Then NewStr = NewStr & "; " End If If InStr(FirstPart, "-") 0 Then FirstNum = Val(Trim(Left(FirstPart, InStr(FirstPart, "-") - 1))) LastNum = Val(Trim(Mid(FirstPart, InStr(FirstPart, "-") + 1))) For NumCount = FirstNum To LastNum If NumCount < FirstNum Then NewStr = NewStr & ", " & NumCount Else NewStr = NewStr & NumCount End If Next NumCount Else NewStr = NewStr & FirstPart End If Loop Range("A" & RowCount) = NewStr End If RowCount = RowCount + 1 Loop End Sub "Macca1984" wrote: Hi, I have ranges of numbers which are inconsistent and random; by this i mean there is no logical order. I have separated the numbers using a dash i.e (45678 - 45688) I want excel to expand the range so that it displays 45678, 45679, 45680, etc up to 45688. The ranges are all in one cell however there is hundreds of cells all with different ranges. Some of the ranges are split up by single numbers to give an example ( 45678 - 45688; 45690; 45693 - 45699) it is important i only get the ranges separated by dashes not the entire range. I would like excel to convert the range and expand it into the same cell as the single numbers with a preference for numerical ordering Any help or assistance is greatly appreciated; i don't want to write this out by hand if possible. Ian |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Expanding Range in Formula? | Excel Discussion (Misc queries) | |||
| Expanding a named range | Excel Programming | |||
| Expanding a range | Excel Programming | |||
| expanding a range of numbers | Excel Discussion (Misc queries) | |||
| Auto expanding Range | Excel Programming | |||
Linear Mode
