Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
separate multiline address into separate columns
I have an excel file where I need to manipulate the billing street and
shipping street fields. The billing and shipping street field might have two or three lines...i.e.: Archdiocese Of Newark School 2 Cedar Street or Kokomo Center Township C.S.D. 100 W. Lincoln Road P.O. Box 2188 I want to separate each line into its own column. Some of the rows have empty columns for the address. I tried running a macro using the following subroutine: Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Next End Sub I get an error that occurs on row 7004, which is thrown on the following line when I run the macro: Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Run-time Error '1004': Application-defined or object-defined error The macro worked perfectly until row 7004, then it threw the error because the next row was blank. I copied all the contents of the billing street into a new worksheet and ran the macro, which parsed the data out into three new fields up to row 7004, then it bombed. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
separate multiline address into separate columns
Try
Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR 'Added to check for empty row If arr(Counter, 1) = "" Then Else arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 End If Next End Sub "-Dman100-" wrote: I have an excel file where I need to manipulate the billing street and shipping street fields. The billing and shipping street field might have two or three lines...i.e.: Archdiocese Of Newark School 2 Cedar Street or Kokomo Center Township C.S.D. 100 W. Lincoln Road P.O. Box 2188 I want to separate each line into its own column. Some of the rows have empty columns for the address. I tried running a macro using the following subroutine: Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Next End Sub I get an error that occurs on row 7004, which is thrown on the following line when I run the macro: Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Run-time Error '1004': Application-defined or object-defined error The macro worked perfectly until row 7004, then it threw the error because the next row was blank. I copied all the contents of the billing street into a new worksheet and ran the macro, which parsed the data out into three new fields up to row 7004, then it bombed. Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
separate multiline address into separate columns
On Tue, 28 Oct 2008 18:48:31 -0500, "-Dman100-" wrote:
I have an excel file where I need to manipulate the billing street and shipping street fields. The billing and shipping street field might have two or three lines...i.e.: Archdiocese Of Newark School 2 Cedar Street or Kokomo Center Township C.S.D. 100 W. Lincoln Road P.O. Box 2188 You can use the Data/Text to columns wizard. Select your column of Data Data/Text to Columns Delimited Next At this stage, under the Delimiters column, check Other. Then, with your cursor in the empty box next to other, hold down the <alt key while typing 010 on the numeric keypad. (Or type <ctrl-J which is the equivalent). Finish --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
separate multiline address into separate columns
Thanks, that got it working. I got this subroutine off the web. If you
don't mind, can you explain what the following lines are doing in this routine? Sub SplitThem() Dim LastR As Long 'These define the variables with the associated datatypes...why long and variant? Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row 'What are these two lines doing? It appears LastR is getting the total number of rows? arr = [a1].Resize(LastR, 1).Value 'Not sure what is happening here Worksheets.Add 'Add a new worksheet For Counter = 1 To LastR 'Looping thru the total number of rows 'Added to check for empty row If arr(Counter, 1) = "" Then 'Check if empty row Else arr2 = Split(arr(Counter, 1), Chr(10)) 'Splitting the data by line break? Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 'Not sure what this line is doing? End If Next End Sub Thanks. "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Try Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR 'Added to check for empty row If arr(Counter, 1) = "" Then Else arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 End If Next End Sub "-Dman100-" wrote: I have an excel file where I need to manipulate the billing street and shipping street fields. The billing and shipping street field might have two or three lines...i.e.: Archdiocese Of Newark School 2 Cedar Street or Kokomo Center Township C.S.D. 100 W. Lincoln Road P.O. Box 2188 I want to separate each line into its own column. Some of the rows have empty columns for the address. I tried running a macro using the following subroutine: Sub SplitThem() Dim LastR As Long Dim Counter As Long Dim arr As Variant Dim arr2 As Variant LastR = Cells(Rows.Count, 1).End(xlUp).Row arr = [a1].Resize(LastR, 1).Value Worksheets.Add For Counter = 1 To LastR arr2 = Split(arr(Counter, 1), Chr(10)) Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Next End Sub I get an error that occurs on row 7004, which is thrown on the following line when I run the macro: Cells(Counter, 1).Resize(1, UBound(arr2) + 1).Value = arr2 Run-time Error '1004': Application-defined or object-defined error The macro worked perfectly until row 7004, then it threw the error because the next row was blank. I copied all the contents of the billing street into a new worksheet and ran the macro, which parsed the data out into three new fields up to row 7004, then it bombed. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change address, city, state zip code into separate columns | Excel Discussion (Misc queries) | |||
I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
separate name and address | Excel Worksheet Functions | |||
Open Excel files in separate sessions, not just separate windows? | Excel Discussion (Misc queries) | |||
Separate address column to Five columns | Excel Worksheet Functions |