Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with Loops

  Asked By: Jeff    Date: Dec 12    Category: MS Office    Views: 804
  

I have a spreadsheet with multiple columns of data. Column A is
filled with a series as follows:

TRNS
SPL
SPL
SPL
TRNS
SPL
SPL
SPL
SPL
SPL
SPL
TRNS

I need to insert two rows immediately before the TRNS then in the
first line enter the word ENDTRNS. I have a macro that does this
already, but I need the macro to repeat until it hits a blank line
(at the end of the data- approx. 5500 lines). Should be easy...?
If anyone can help it would be appreciated..

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Guadalupe Rogers     Answered On: Dec 12

Sub blah()
Do While ActiveCell <> ""
If ActiveCell = "TRNS" Then
Selection.EntireRow.Insert Shift:=xlDown
Selection.EntireRow.Insert Shift:=xlDown
ActiveCell = "ENDTRNS"
ActiveCell.Offset(2, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

First select the top of column  A where you want this to start from (It
might be a good idea to start one cell down from the first "TRNS")
then run the macro

 
Answer #2    Answered By: Gustavo Taylor     Answered On: Dec 12

but it doesn't work for more than one insert. I
think because it hits the second blank line  after the "ENDTRNS"
entry?

TRNS
SPL
SPL
SPL
ENDTRNS
<-STOPS HERE
TRNS
SPL
SPL
....

 
Answer #3    Answered By: Velma Adams     Answered On: Dec 12

Let's discuss debugging for a moment: Pascal's code is given again below
for discussion purposes.

Sub blah()
Do While ActiveCell <> ""
If ActiveCell = "TRNS" Then
Selection.EntireRow.Insert Shift:=xlDown Selection.EntireRow.Insert
Shift:=xlDown ActiveCell = "ENDTRNS"
ActiveCell.Offset(2, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Get Excel and the VBE side by side on one screen (even better if you
have two monitors), and step through the code execution with F8. If you
do not know how to do this, select an appropriate cell in Excel for the
code to start, position your position your cursor anywhere in the
routine Blah(), and then press F8. Now, you can watch what Excel does
during each step of the code execution.

Pascal's code works like it should on my machine. Let us know the
debugging results.

 
Didn't find what you were looking for? Find more on Help with Loops Or get search suggestion and latest updates.




Tagged: