Split cells VBA
in [Programming]
|
From: Diddy on 3 Mar 2010 09:37 Hi Bob, Being truthful, the basic problem is lack of understanding of objects etc so when I had anything out of the ordinary (i.e. the different wkbks had sheets which had names specific to the wkbk) I didn't know what to do :-( Cheers Diddy (learning very slowly!) "Bob Phillips" wrote: > 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: Diddy on 3 Mar 2010 09:38
Thank you Per, Cheers Diddy "Per Jessen" wrote: > 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 > > . > |