I’m sure this is somewhere on Google or here in the lounge, but my brain has turned to cream cheese.
I’m trying to move down one column and move the data to another. The field contains something like
“SUBCOUNT 10″. I want to move the data from column A to column D, then delete the text part, leaving only the number. This starts in, say A5.
Cells.Find(What:=”subcount”, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Cut
ActiveCell.Offset(-1, 4).Range(“A1”).Select (moves to column D)
ActiveSheet.Paste
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 9), Array(2, 1)), TrailingMinusNumbers:=True (removes all but the number value)
ActiveCell.Offset(1, -4).Range(“A1”).Select (returns to column A)
‘Loop here
The code does some other stuff like deleting totally blank rows to keep things neat while it works. Where I’m stuck is, I want it to loop until it gets to, say A2500, but I can’t figure it out the syntax or command. If I use a loop with a counter, it can go to another location if the word subcount still exists and mess that up. I can do loops with counters easily, but this one has me beaten. Thanks for any help.
