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
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