paspuggie48
New Member
- Joined
- Jun 21, 2011
- Messages
- 35
Hi guys n dolls
I am currently and painstakingly putting together an excel spreadsheet that imports horse racing data and have created a few macros to sort the data into something that is legible. However I am now stuck on one thing and that is I would like to find a word "Class" in Column C then split (using Text To Column) the text into separate columns.
Here is the code so far...apologies but I don't know how to past the text into code etc but here goes
My question is, the word Class could be anywhere (depending on the amount of runners) I have tried "find next" scenarios but nothing seemd to work. Recording a macro to go to the next word "Class" only went to the cell range I clicked, example above "C13"
So, can it be done where it finds "Class", performs the split, then goes to the next cell containing "Class" which is down the column, re-perform the split adn lopp until there is no text in the column?
Regards
Paul
I am currently and painstakingly putting together an excel spreadsheet that imports horse racing data and have created a few macros to sort the data into something that is legible. However I am now stuck on one thing and that is I would like to find a word "Class" in Column C then split (using Text To Column) the text into separate columns.
Here is the code so far...apologies but I don't know how to past the text into code etc but here goes
Code:
Sub Split_Text()
'************
'Find cell text containing "Class" in the sentence
'Example : Class 1, £55000.00 added, 3yo plus, £31190.00 penalty
'************
Cells.Find(What:="Class*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
'************
'Use Text to Column to split the text into separate columns
'************
Selection.TextToColumns Destination:=Range("C13"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(7, 9), Array(8, 1), Array(24, 9), Array(25, 1), _
Array(34, 9), Array(35, 9)), TrailingMinusNumbers:=True
End SubSo, can it be done where it finds "Class", performs the split, then goes to the next cell containing "Class" which is down the column, re-perform the split adn lopp until there is no text in the column?
Regards
Paul
