Hi everybody -
I am hoping someone can help me out with a problem I am having. I am
very new to writing and recording macros, I have been trying to look
at other macros that I use for different functions and tips.
I have a macro that I am trying to get to do a few different things.
The macro works up until a point and then it does not do what I am
hoping it would. I first do a text-to-columns on the data and insert
a column and fill the cells, which works fine, and then I am trying to
do a Find on column B to identify the first cell with a "U" in it.
Once this cell is found I am trying to move the cursor one cell to the
left, select everything from that cell to the end, copy and paste the
data into sheet 3. In sheet 3, another Find is done in column B to
find the first cell with a "us" in it, move the cursor one cell to the
left, select everything from that cell to the end and delete all of
the data.
I am having the biggest problem right now getting the cursor to move
over after the Find has been done, as it is probably apparent in my
code below. I have been trying a few different things with no luck.
I saw a sendkeys command in one of my other macros and tried to
integrate that, but I do not believe I did it correctly at all. I am
hoping someone can show me how to get the macro to use the find to
identify the correct cell and select the cell next to the one
identified with the Find function.
Here is my current code, any help would be greatly appreciated!
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(2, 1), Array(13, 1),
Array(19, 1), Array(27, 1), _
Array(28, 1), Array(36, 1), Array(66, 1), Array(81, 1),
Array(105, 1), Array(131, 1)), _
TrailingMinusNumbers:=True
Selection.Insert Shift:=xlToRight
Range("A1").Select
ActiveCell.FormulaR1C1 = "1"
Range("A1").Select
Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear,
Date:=xlDay, _
Step:=1, Stop:=30000, Trend:=False
Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("B:B").Select
Selection.Find(What:="u", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
SendKeys "({LEFT})", [FALSE]
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Columns("B:B").Select
Selection.Find(What:="us", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
SendKeys "({LEFT})", [FALSE]
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Sheet1").Select
Range("A1").Select
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
