VBA to count rows from specific cell and insert rows
 

Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie

VBA to count rows from specific cell and insert rows
 

not sure where you want to start, but this may give you an idea on how to do
what you want

Worksheets("Sheet1").Range("A1").Offset(170).Resiz e(2).EntireRow.Insert

--


Gary


"Valerie" wrote in message
...
Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie

VBA to count rows from specific cell and insert rows
 

Hi,
try something like

DIm rg as range

''' where to search
set rg=activesheet.range("U:U")
''' search for 'found'
set rg=rg.find(what:="found", lookin:=xlvalues, lookat:=xlwhole)
''' process result
if rg is nothing then ''' was not fond
msgbox "Not found"
else
''' go 170 rows bellow that found cell
set rg=rg.offset(170)
''' resize to 2 rows
set rg= rg.resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
end if

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Valerie" wrote:

Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie

VBA to count rows from specific cell and insert rows
 

Works like a charm!! Thanks!!

"sebastienm" wrote:

Hi,
try something like

DIm rg as range

''' where to search
set rg=activesheet.range("U:U")
''' search for 'found'
set rg=rg.find(what:="found", lookin:=xlvalues, lookat:=xlwhole)
''' process result
if rg is nothing then ''' was not fond
msgbox "Not found"
else
''' go 170 rows bellow that found cell
set rg=rg.offset(170)
''' resize to 2 rows
set rg= rg.resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
end if

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Valerie" wrote:

Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie