Split cells VBA
in [Programming]
|
From: Diddy on 3 Mar 2010 08:12 Hi everyone, I've been using the following (kindly supplied by Rick Rothstein) to split address items. Sub SplitCells() Dim X As Long Dim Z As Long Dim LastRow As Long Dim Sections() As String With Worksheets("Sheet1") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = 2 To LastRow Sections = Split(.Cells(X, "A").Value, "/") For Z = 0 To UBound(Sections) ..Cells(X, Z + 2).Value = Sections(Z) Next Next End With End Sub I now have lots of single sheet wkbks which have named sheets. I've tried taking the " " out from the With Worksheets("Sheet1") and also calling it Activesheet getting very mixed up :-( If anyone can help (possibly explain what's foxing me) that would be great! Thanks Diddy
From: Bob Phillips on 3 Mar 2010 08:30 What is the problem, the plethora of workbooks, the desire to process multiple sheets, or what? -- HTH Bob "Diddy" <Diddy(a)discussions.microsoft.com> wrote in message news:A1DB568C-2725-43CD-9472-F31D3EF7843A(a)microsoft.com... > Hi everyone, > > I've been using the following (kindly supplied by Rick Rothstein) to split > address items. > > Sub SplitCells() > Dim X As Long > Dim Z As Long > Dim LastRow As Long > Dim Sections() As String > With Worksheets("Sheet1") > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > For X = 2 To LastRow > Sections = Split(.Cells(X, "A").Value, "/") > For Z = 0 To UBound(Sections) > .Cells(X, Z + 2).Value = Sections(Z) > Next > Next > End With > End Sub > > I now have lots of single sheet wkbks which have named sheets. I've tried > taking the " " out from the With Worksheets("Sheet1") and also calling it > Activesheet getting very mixed up :-( > > If anyone can help (possibly explain what's foxing me) that would be > great! > > Thanks > Diddy
From: Mike H on 3 Mar 2010 08:33 Hi, Activesheet shout work in a single sheet workbook so you could try With ActiveSheet This should work also With Sheet1. Note we've now dropped the quotes because were not using a sheet name -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Diddy" wrote: > Hi everyone, > > I've been using the following (kindly supplied by Rick Rothstein) to split > address items. > > Sub SplitCells() > Dim X As Long > Dim Z As Long > Dim LastRow As Long > Dim Sections() As String > With Worksheets("Sheet1") > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > For X = 2 To LastRow > Sections = Split(.Cells(X, "A").Value, "/") > For Z = 0 To UBound(Sections) > .Cells(X, Z + 2).Value = Sections(Z) > Next > Next > End With > End Sub > > I now have lots of single sheet wkbks which have named sheets. I've tried > taking the " " out from the With Worksheets("Sheet1") and also calling it > Activesheet getting very mixed up :-( > > If anyone can help (possibly explain what's foxing me) that would be great! > > Thanks > Diddy
From: Per Jessen on 3 Mar 2010 08:52 Hi Diddy, Try one of theese: With Worksheets(1) or With ActiveSheet Regards, Per "Diddy" <Diddy(a)discussions.microsoft.com> skrev i meddelelsen news:A1DB568C-2725-43CD-9472-F31D3EF7843A(a)microsoft.com... > Hi everyone, > > I've been using the following (kindly supplied by Rick Rothstein) to split > address items. > > Sub SplitCells() > Dim X As Long > Dim Z As Long > Dim LastRow As Long > Dim Sections() As String > With Worksheets("Sheet1") > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > For X = 2 To LastRow > Sections = Split(.Cells(X, "A").Value, "/") > For Z = 0 To UBound(Sections) > .Cells(X, Z + 2).Value = Sections(Z) > Next > Next > End With > End Sub > > I now have lots of single sheet wkbks which have named sheets. I've tried > taking the " " out from the With Worksheets("Sheet1") and also calling it > Activesheet getting very mixed up :-( > > If anyone can help (possibly explain what's foxing me) that would be > great! > > Thanks > Diddy
From: Diddy on 3 Mar 2010 09:32
Hi Mike, Both worked :-) Cheers Diddy "Mike H" wrote: > Hi, > > Activesheet shout work in a single sheet workbook so you could try > > With ActiveSheet > > This should work also > > With Sheet1. > > Note we've now dropped the quotes because were not using a sheet name > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "Diddy" wrote: > > > Hi everyone, > > > > I've been using the following (kindly supplied by Rick Rothstein) to split > > address items. > > > > Sub SplitCells() > > Dim X As Long > > Dim Z As Long > > Dim LastRow As Long > > Dim Sections() As String > > With Worksheets("Sheet1") > > LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row > > For X = 2 To LastRow > > Sections = Split(.Cells(X, "A").Value, "/") > > For Z = 0 To UBound(Sections) > > .Cells(X, Z + 2).Value = Sections(Z) > > Next > > Next > > End With > > End Sub > > > > I now have lots of single sheet wkbks which have named sheets. I've tried > > taking the " " out from the With Worksheets("Sheet1") and also calling it > > Activesheet getting very mixed up :-( > > > > If anyone can help (possibly explain what's foxing me) that would be great! > > > > Thanks > > Diddy |