what's the Bug....tell me what's happening???

[foamfollower]

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

 

[pikus]

We meet again.

z = Selection.Rows.Count
For x = 1 To z
Selection(1, 1).EntireRow.Insert
Next x

This work for you? - Pikus

 

[Tom Ogilvy]

Selection.EntireRow.Insert

will insert entire rows.

You don't have any merged cells do you?

Turn on the macro recorder while you do

clicking on a single cell in column J (blank cells column),

then right mouse clicking, to Insert, then Insert Entire Row.

This is what I get:

Range("J10:J12").Select
Selection.EntireRow.Insert


When I run the recorded macro, it inserts 3 entire rows.

However, as written, you have entirerows selected, so it should be inserting
entirerows anyway.

 

[foamfollower]

You guys are a great help!!
Of course, all suggested codes DO work, and are greatly appreciated.
I think my problem was, i was trying to copy a selection and then do
all the inserting rows stuff, then paste the selection. All sorts of
weird things were happening with the different Insert methods. i
don't think you can hold a copied selection thru an Insert function,
now that i think about it. copied selections do hold up rather well
thru procedures these days, though, which is nice.

anyway, i have this procedure now to:
ask for sample number retested
then insert rows to accomidate retest data
then copy and paste retest data
then color retest cells

however, there are frequently multiple samples retested. the retest
data will
always be the same size, ex: c8:k19 (retest set one) c20:k31 (retest
set two),
etc.
I would like the input box to keep appearing until the user clicks a
button
called "Finished". i think i would have to define the range size, and
keep looping to the next range size, but i'm guessing. if this works,
it would be my first looping procedure....that's pretty cool!

her e is what i have that works really well...once,

Sub InsertRetest()

req = InputBox("what number was retested", , "enter roll #")

Sheets("data").Select
Range("d8:d10000").Find(req).Offset(12, -3).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 11).Select
Selection.EntireRow.Insert

Sheets("retest data").Select
Range("c8:k19").Select

Application.CutCopyMode = False
Selection.Copy
Sheets("data").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
With ActiveCell.Resize(12, 9).Interior
.ColorIndex = 36
End With
ActiveCell.Select

End Sub

Thanks for any suggestions!

SF

 

[Tom Ogilvy]

Sub InsertRetest()

Do
req = InputBox("what number was retested", , "enter roll #")
if req = "" then exit sub
Sheets("data").Select
set rng = Range("d8:d10000").Find(req).Offset(12, -3)
if rng is nothing then
msgbox "Not found exiting"
exit sub
End if
rng.Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row + 11).Select
Selection.EntireRow.Insert

Sheets("retest data").Select
Range("c8:k19").Select

Application.CutCopyMode = False
Selection.Copy
Sheets("data").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
With ActiveCell.Resize(12, 9).Interior
.ColorIndex = 36
End With
ActiveCell.Select
Loop
End Sub