VBA to count rows from specific cell and insert rows

[Valerie]

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

 

[Gary Keramidas]

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).Resize(2).EntireRow.Insert

 

[sebastienm]

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

 

[Valerie]

Works like a charm!! Thanks!!

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