+ Reply to Thread
Results 1 to 2 of 2

Macro to find a cell containing text, select range offset, cut and paste to lower rows

  1. #1
    Registered User
    Join Date
    02-04-2014
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel: mac 2011
    Posts
    1

    Macro to find a cell containing text, select range offset, cut and paste to lower rows

    I am trying to find a macro that can search a sheet for any cell that contains the text “Not on AOI,” selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the “Not on AOI” cells is variable.

    I’m very new to excel macros and the parts I think I’ve put together are:

    Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=True).Activate

    Range(ActiveCell,ActiveCell.Offset(81,2000)).Select
    Selection.Cut
    ActiveCell.Offset(162,0).Select
    Selection.Paste

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,620

    Re: Macro to find a cell containing text, select range offset, cut and paste to lower rows

    store location in a variable and then use it
    with variable - you can check if anything has been found (if not it is set to Nothing)

    Please Login or Register  to view this content.
    But now - how do you plan to make sure next search wil not find the same cell?


    PS note how tidy is a code in the code marks - anyway it is requirement of http://www.excelforum.com/forum-rule...rum-rules.html
    Last edited by Kaper; 02-04-2014 at 01:59 PM.
    Best Regards,

    Kaper

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro to find text string in cell range and paste in other cell range
    By slearner1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2013, 11:15 AM
  2. [SOLVED] Macros to find matching text on other sheet and to select an offset cell
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-15-2012, 05:59 AM
  3. find and select a cell to paste a range
    By gjolson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-30-2012, 07:56 PM
  4. Find Text, Select range below, copy, paste to new work sheet
    By kim5012 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2011, 07:10 PM
  5. Create macro to paste rows of text cells to lower end of other row
    By RodFerd in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-20-2006, 09:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1