PDA

View Full Version : Macro to find a cell containing text, select range offset, cut and paste to lower row



sdaniels
02-04-2014, 10:29 AM
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.

For a simplified example, I want to take a sheet that looks like this:


NHA01

NHA01-Not on AOI

NHA04

NHA04

NHA04- Not on AOI

NHA07

NHA07

NHA07- Not on AOI



1

1

5

0

0

7

2

2




And make it look like:


NHA01

















1



















NHA01-Not on AOI

NHA04

NHA04













1

5

0



















NHA04- Not on AOI

NHA07

NHA07













0

7

2



















NHA07- Not on AOI

















2



Notice the number of columns between “Not on AOI” 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

david000
02-04-2014, 12:53 PM
Welcome,

There's only 16,384 columns in Excel and you can only go to the right 2000 columns about 8 times. So, in your example it doesn't look like the phrase Not on AOI is 2000 columns apart. It looks like your wanting to "step" these instances across the sheet. How many times are you looking at doing that?

sdaniels
02-05-2014, 08:43 AM
Welcome,

There's only 16,384 columns in Excel and you can only go to the right 2000 columns about 8 times. So, in your example it doesn't look like the phrase Not on AOI is 2000 columns apart. It looks like your wanting to "step" these instances across the sheet. How many times are you looking at doing that?

Yes, I want to step columns down with one "Not on AOI" column per step, it should come to between 500 and 600 "steps." I don't really need to select 2000 columns for each step, just every column to the right that has data (there are just under 2000 columns to the right of the first "Not on AOI" instance). There should be between 1 and 7 columns between each "Not on AOI" column, so each step should be between 2 and 8 columns wide. The trouble I'm having is that the number of columns between each "Not on AOI" is random.

Thank you for your help, as stated I am very new at this.