Hi Paul -
Thank you for your reply.
I have added in your suggestion, but unfortunately I am still having a
bit of an issue.
The find now activates the correct cell, and the offset moves the
active cell one to the left, but it is only selecting that particular
row to copy and paste into the other tab. I am trying to figure out
how to get it to select everything from the active cell to the bottom
of the file, and copy and paste all of those rows. I keep poking and
prodding with it, but I have had no luck. After adjustment, my code
is:
Selection.Find(What:="u", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
Range(ActiveCell.Offset(0, -1), Cells(ActiveCell.Row,
ActiveCell.SpecialCells(xlLastCell).Column)).Select
Selection.Copy
Sheets("Sheet3").Select
Range("A1").Select
ActiveSheet.Paste
Thank you again!
On Sep 30, 12:14 pm, Paul Schreiner <schreiner_p...@att.net> wrote:
> First of all, your code is very "selection" intensive.
> That is, really what you're doing is automating keystrokes.
> as long as you don't have a LOT of data to process, it should
> work. It's just not efficient.
> You COULD rewrite it using VBA variables and not "select" as much.
> But we'll work with what you have.
>
> --------------------------------------------
> What does your "Selection.Find" return?
>
> Selection.Find(What:="u", After:=ActiveCell, LookIn:=xlFormulas, _
> LookAt :=xlWhole, SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, MatchCase:=False, _
> SearchFormat:=False).Activate
>
> it returns the .Activate method... that is, it "activates" the cell.
>
> Now, if you want to select the cell to the left of the active cell.
> you can use the Offset() method:
> ActiveCell.Offset(0, -1).Select
>
> or, you can use something similar to what you did by using the cells()
> method.
> Now, your use of ActiveCell.SpecialCells(xlLastCell) is close... but the
> problem
> is that you only want the COLUMN of the last cell, and the current row.
> So, you could use:
> Cells(ActiveCell.Row, ActiveCell.SpecialCells(xlLastCell).Column)
>
> combining the two you get:
>
> Range(ActiveCell.Offset(0, -1), Cells(ActiveCell.Row,
> ActiveCell.SpecialCells(xlLastCell).Column)).Select
>
> try using that and see what you can do..
>
> if you need more help, let me know.
>
> Paul
>
>
>
> ----- Original Message ----
> > From: Erick C <boricua2...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thu, September 30, 2010 12:10:35 PM
> > Subject: $$Excel-Macros$$ Excel Macro - Need help using Find and Selecting a
> >range
>
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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&...-
> >Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
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
