Hi,
i originally posted message (located at end) and received a nice reply
on how to take a message box response, then locate a value in a range,
insert cells in the appropriate spot. with some additions, i now have
a nice procedure.
HOWEVER, THE INSERT FUNCTION DOES NOT APPEAR TO WORK CORRECTLY, or any
variation i've tried.
Basically, i cannot 'manually' create what is happening. when i use
the
line to insert rows: Selection.Insert Shift:=xlDown , entire rows
are
NOT inserted. i have tried a few other variations, and all don't
work, including putting the EntireRows property in.
when i'm inserting rows manually, no matter what combination of cells
i choose, entire rows are always inserted, from column A to Column
256.
in the macro, however, only rows are inserted until the first column
with 'an empty cell' is reached. (and adding numbers or text in these
columns doesn't work) This results in formulas not adjusting
correctly at all. always, when i insert rows manually, the formulas
for the 'next' sample, after the inserted rows, moves DOWN
accordingly. yet, when macro used Insert, the formulas don't move at
all.
basically, i have a big block of data in columns A:I, and then two
blank columns, and then various basic analysis formulas in columns
L:S, for each sample.

Can someone please tell me what's going on? i just tried this
manually. clicking on a single cell in column J (blank cells column),
then right mouse clicking, to Insert, then Insert Entire Row. An
entire row was inserted, moving all formulas down accordingly (and
increasing the range for the current samples formulas, of course).
But, why doesn't the various macro methods work?

Hopefully, somebody knows one that does.

Thanks and Take Care!

SF

one current trial/error code:
Sub select_10()

' i added "enter roll #" where a 2 was. i changed a1 to a10 (not
a11). seems to work
' for all roll numbers. did not work for roll 1 before.

'Sheets("data").Select
req = InputBox("what number was retested", , "enter roll #")
'the following is the re-test data to be inserted.
Range("c8:k19").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("data").Select
Range("d8:d10000").Find(req).Offset(12, -3).Select
'Cells(ActiveCell.Row, 1).Activate
'ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 11).Select
'Selection.EntireRow.INSERT
Selection.Insert Shift:=xlDown
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False

'ActiveCell.Offset(0, 1).Select
ActiveCell.Select

With ActiveCell.Resize(12, 9).Interior
.ColorIndex = 36

End With

End Sub

original message:
Hi

have a look at the following. It should help. Note that every time
new rows are inserted, the range to look for the number will change.

Tony

Sub ccc()
req = InputBox("what number was retested", , 2)
Range("a1:a100").Find(req).Offset(10, 0).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 9).Select
Selection.Insert Shift:=xlDown
End Sub
----- foamfollower wrote: -----

Hello,
My latest issue is this:
Let's say there is a list of numbers in cells A1:A100. These
numbers
are 1-10, in groups of 10. example: cells A1:A10 all contain 1,
cells
A11:A20 all contain 2, and on up to 10.
I will need to insert 10 more rows according to whatever number
is
entered into an Input Box or the like.
the user will click button to 'insert retest', then Input Box
shows to
ask
what 'number' was retested, the user enters the number (say 5),
then
10
rows are automatically inserted after the last number 5 in the
range.

sounded like easy navigation manipulation in my head...but i'm
still
new at this. Any help from the Masters is greatly appreciated.

SF