Copy/Insert to next row based on cell value

Microsoft Excel 2003 (full product)
September 28, 2010 at 14:22:53
Specs: Windows XP
I am trying to write a macro that will copy and insert the entire row below it if column "B" equals "DIV" then change the row it copied - column "i" to 1 and then check the following row for the word "DIV" in column "B" and do the same (loop).

Any help would be appreciated. Thanks!

Cheryl


See More: Copy/Insert to next row based on cell value

Report •


#1
September 28, 2010 at 21:46:54
re: "I am trying to write a macro..."

Well, what have you tried so far?

How about posting what you have and we'll see if we can make some suggestions.

Learning by doing is a good thing. I like to hear that.


Report •

#2
September 29, 2010 at 11:21:36
Hi DerbyDad03,

This is what I have and it is not working. It copies and pastes to the next row as I want but then it doesn't go to the next row to see if there is a "DIV" in Column "D".


Dim LSearchRow As Integer
Dim LCopyToRow As Integer

'Start search in row 2
LSearchRow = 2
If Range("D" & CStr(LSearchRow)).Value = "DIV" Then
'Select row to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy
Selection.Insert Shift:=xlDown
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

End If

End Sub

I would also like it to change Column "I" in the original row it copied to "1" after it copies and pastes, and the row it pastes change Column "D" to "BUY". I don't know if it is all possible but it would be nice.

Thanks!

Cheryl


Report •

#3
September 29, 2010 at 12:03:14
We should start with the fact that you are lying to us. ;-) I'm kidding - sort of.

You said: "It copies and pastes to the next row as I want"

No it doesn't!

You have a While statement but no Wend so the code throws up an error as soon as you try to run it.

So, let's start by adding a Wend before the End Sub:

 Wend
End Sub

Next, you set your row number LSearchRow to be 2, but you never increment it to test another row, so it just keep checking Row 2.

Let's increment that variable before we loop back to the top:

  LSearchRow = LSearchRow + 1
 Wend
End Sub

Next, you want to know if changing values in certain cells is possible. Absolutely!

Range("I" & CStr(LSearchRow)) = 1

Finally, there is some stuff we can clean up. Rarely do you have to "Select" an object in VBA in order to perform and action on it. Selecting objects just slows the code down and makes it less efficient. You can usually just refer to the object directly:

e.g This:

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

can be reduced to this:

Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Copy

In fact, it can be reduced further to this:

Range("D" & CStr(LSearchRow)).EntireRow.Copy

Many of your other "Selects" seem to be junk left over from a recorded macro, so we're going eliminate them also.

In the end, I think this gets you what you want:

Option Explicit
Sub CopyInsertChangeData()
Dim LSearchRow As Integer
'Start search in row 2
 LSearchRow = 2
'Loop while Column A <> "
  While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If Column D = "DIV", copy entire row
   If Range("D" & CStr(LSearchRow)).Value = "DIV" Then
    Range("D" & CStr(LSearchRow)).EntireRow.Copy
'Insert copied Row below the original
    Range("D" & CStr(LSearchRow)).EntireRow.Insert Shift:=xlDown
'Change values in Columns I (original) and D (New)
    Range("I" & CStr(LSearchRow)) = 1
    Range("D" & CStr(LSearchRow + 1)) = "Buy"
   End If
'Increment Search row variable
   LSearchRow = LSearchRow + 1
'Loop
  Wend
End Sub

Keep trying to work with VBA! You're doing great.

P.S. Do you know how to Single Step through your code to test it?


Report •

Related Solutions

#4
September 29, 2010 at 12:05:55
Sorry I just updated the post I sent. It does work now. I had to take a line out and get rid of the Wend. I have been working on this and saving macros as I go along and I posted the wrong one! :)

Report •

#5
September 29, 2010 at 12:19:10
I hope you did see my updated macro - it does work. This was my first macro and I have been pulling out my hair trying to figure it out. I do not know how to single step through code to make sure it works. This is just the first of MANY I will be creating. Thank you so much for you help! I have a lot of work to do :) I will keep this website just in case I do need more help.

Thanks Again!!!

Cheryl


Report •

#6
September 29, 2010 at 12:43:18
A while back (9 years, I just found out!) I posted this in another forum.

It's a list of tips that I picked up along the way while teaching myself how to use VBA.

Single Stepping and Watches are 2 very powerful tools to use when writing and debugging code.

Debugging Visual Basic Code 101- A Tutorial
=================================

Lesson 1 - Single Stepping

- Open the VBA editor.
- Place the cursor anywhere within the code.
- Hit F8 to begin Single Stepping through the code.

The next line to be executed will be highlighted, and execution will take place the next time you hit F8.

You can make changes to the code while in Single Step mode. Some changes will alert you that the Debugger will stop if you make that change, other changes will be accepted without interruption to the code.

You can quickly Single Step through the code by holding down the F8 key.

You can drag the step arrow down to an executable line and begin Single Stepping from there. You can also skip lines by dragging the arrow down at any time and re-run lines by dragging the arrow up. Keep in mind that you might get errors if the line you are trying to execute needs a value from a line that you skipped. If you skipped a line that set x = 8 but you don't want to run all the rest of the code around that line, simply type in a line that says x = 8 above the section that needs that value, place the Step arrow next to that line and begin stepping.

You can exit Single Step mode by clicking on the blue square in the tool bar.

You can click on the Run arrow at any time to let the macro finish on its own.

TIP: If you size and place your VBA editor window so that you can see your spreadsheet behind it you should be able to see your spreadsheet change as the code is executing. Fun and entertaining, yes...but also very valuable for
troubleshooting.

Lesson 2 - Pop Up Values

As you single step through your code, hold your cursor over a variable. The current value of that variable should pop up. Hold your cursor over things like Range("A1").Value. The current value that is in that cell should pop up. There are certain items that will not pop up a value and the method used to obtain those values will be covered later in the next section. Looking
at the Pop Up values is great way to see what your variables are getting set to as the code is running.

Lesson 3 - Adding Watches

- Highlight a variable or any other value-producing entity that you are interested in "watching" as you single step through the code.
- Hit Shift-F9.
- Hit Enter or click OK.
The name of the item will appear in a window in the bottom portion of the VBA editor. Add as many items as you would like. As you Single Step through your code, the current value of those items will appear next to the name.

Note: You can also highlight and drag the item into the Watch window if the window is already open.

In many cases, an item that would have produced an error if executed will show that error in the Watch window as soon as you enter Single Step mode.

There are items that won't pop up their values with the cursor held over them, but they will show their value in the Watch window.

TIP: If you are single stepping through a long loop, add a Watch on the counting variable (e.g. the x in For x = 1 to 100). Hold down the F8 key and watch as the x in the Watch window increments. Release the F8 key as the x approaches 100 and then use F8 at your leisure to finish the loop and continue stepping through your code.

Lesson 4 - Run To Cursor

A particularly useful item found under the Debug menu is Run To Cursor.

Let's say you know the first half of your code works fine, but something in the last half is giving you an error. This is where Run To Cursor comes in very handy.

- Click on any executable line in the code.
- Pull down the Debug menu and choose Run To Cursor, or press Ctrl-F8.

The macro will run at full speed until it reaches the line with the cursor.

It will then highlight that line and you can begin Single Stepping from there. Very useful for getting past long loops that you know are OK.

Lesson 5 - The Debug Menu

Pull down the Debug menu and investigate the other items found there.

For example, Toggle Breakpoint. If you set a breakpoint at any line, the code will run until it reaches the breakpoint and then exit the macro. There are many other items under the Debug menu to help debug your code.

I'm sure others will offer more methods for debugging code, but Single Stepping and Watches are two very powerful Debugging tools.


Report •

#7
September 30, 2010 at 04:36:06
Thank you again! I will keep this info handy!

Report •

#8
September 30, 2010 at 05:23:31
One more change if this is possible. Now that I am working with this I found a problem. I do not want it to copy "If Range("D" & CStr(LSearchRow)).Value = "DIV" Then Range("D" & CStr(LSearchRow)).EntireRow.Copy"

if column "I" = 0 - only if it is > 0

AND - if column "F" = "ABC" or "DEF" I want it to copy column "J" to "I".

I am confused on where to insert this in the macro. I will be testing today and see if I can come up with anything.


Report •

#9
September 30, 2010 at 07:08:25
I'm a tad confused by your lastest request.

I get the "don't copy DIV row if I = 0" part. That makes sense.

However, the Column F part is confusing.

1 - Do you want the "copy J to I when F = ABC or DEF" task to happen every time F = ABC or DEF or do you only want to happen when a new row is insert?

2 - (This question depends on the answer to Question 1) Do you want the copy to occur on the original row, the new row, or both?

One last note:

Don't take this the wrong way, but we often get questions in this forum where requirements come in drips and draps.

"I need a macro to this"

"Thanks, now can you make it do this too?"

"Hey, that's great. Now, how do I add this?"

That gets a bit annoying since we have to go back and modify the code, sometimes even rewriting it completely so we don't end up with inefficient, cobbled together code.

In addition, we (well, at least I) don't save every workbook that we set up to test our code. When additional requirements come along later, we (I) usually have to copy the code from the forum, set up the workbook with data in the correct columns, etc.

Since I don't want to just add lines to code without testing it, I have to go through extra (repeated) work to test it.

Think about managing a project and asking someone to quote on it or even implement it. Once you start adding requirements after the initial work has begun, things get messy - and usually more expensive.

Anyway, answer my questions above and I'll see what I can suggest.


Report •

#10
September 30, 2010 at 07:16:02
I totally understand what you are saying.

Column "F" is an addition to the entire macro.

Do you want the "copy J to I when F = ABC or DEF" task to happen every time - YES

Column "F" will NEVER be a new row. That is why I am confused on where to put it - I don't know if I should put it before the "If Range ("D")....." or at the end of the macro.

I might keep adding to this with changes in the future. Since it is the first I have created - I will learn.


Report •

#11
September 30, 2010 at 07:53:41
Got the first part down:

Option Explicit

Sub CopyInsertChangeData()

Dim LSearchRow As Integer

'Start search in row 2
LSearchRow = 2
'Loop while Column A <> ""
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If Column D = "DIV", copy entire row
If Range("D" & CStr(LSearchRow)).Value = "DIV" And Range("I" & CStr(LSearchRow)).Value > 0 Then
Range("D" & CStr(LSearchRow)).EntireRow.Copy
'Insert copied Row below the original
Range("D" & CStr(LSearchRow)).EntireRow.Insert Shift:=xlDown
'Change value in Columns I (oringal) and D (New)
Range("I" & CStr(LSearchRow)) = 1
Range("D" & CStr(LSearchRow + 1)) = "BUY"
End If
'Increment Search row variable
LSearchRow = LSearchRow + 1
'Loop
Wend
End Sub


Report •

#12
September 30, 2010 at 08:15:45
Good! OK, now look at my code in Response #3 and then look at yours.

See the difference? Which one's easier to read? (Hint: the correct answer is that mine is easier to read.)

When posting code in this forum, click the pre icon above the Reply box and paste your code between the tags. It will retain it's indented format and be easier to follow. (That's the reason for the indenting when useing the VBA editor - to help the reader follow what is going on inside loops, etc.)

You can also you the pre tags to line up data like so:

   D       F       I      J
  DIV     ABC      1      0

Now, based on the example above, and looking at your requirements individually, J should be copied to I because F = ABC and the row should be Copied/Inserted below because I = 1.

However, if we copy J to I first, then I will = 0 and we shouldn't copy the row.

What do you want to happen in this case? Copy J to I first, then test for D = DIV and I > 0, or copy the row first and then copy J to I in both rows, and then set D to BUY and I back to 1?

Do you see how your various requirements overlap and impact what happens when?


Report •

#13
September 30, 2010 at 09:49:33
Yours is definitely easier than mine - can you tell I am a newbie. So this is what I found:

I did this with a different approach and it worked. I noticed that when "I" ABC or DEF ect. the "I" Column is blank. So This is what I came up with and it worked.

Option Explicit

Sub TD()

Dim LSearchRow As Integer

'Start search in row 2
LSearchRow = 2
'Loop while Column A <> ""
While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If Column D = "DIV", copy entire row
If Range("D" & CStr(LSearchRow)).Value = "DIV" And Range("I" & CStr(LSearchRow)).Value > 0 Then
    Range("D" & CStr(LSearchRow)).EntireRow.Copy
'Insert copied Row below the original
Range("D" & CStr(LSearchRow)).EntireRow.Insert Shift:=xlDown
'Change value in Columns I (oringal) and D (New)
    Range("I" & CStr(LSearchRow)) = 1
    Range("D" & CStr(LSearchRow + 1)) = "BUY"
Else
'If Column I Column is Blank copy column J to I
If Range("I" & CStr(LSearchRow)).Value = "" Then
   Range("I" & CStr(LSearchRow)).Value = Range("I" & CStr(LSearchRow)).Offset(0, 1).Value
   
End If
End If
'Increment Search row variable
    LSearchRow = LSearchRow + 1
    'Loop
    Wend
    End Sub



Report •

#14
September 30, 2010 at 11:45:12
I'm glad you came up with a solution that works for you.

There is are 2 more things I'd like to add about writing VBA code.

When you use indents properly, it really helps make the code easier to follow for the reader. Keep in mind that a year from now, you might be the reader trying to remember what you were trying to do.

For example, take a look at the code you posted in Response #13. Notice that all of your loops are left justified. Typically...


  1 - The first loop is indented slightly
      from the code above it.
    2 - If there is a loop within that loop it is
        indented a little bit more
       3 - If there is a loop within that loop it is
           indented further. 

            Within each loop, the instructions 
            to be executed are indented from 
            the first line of the loop. 

       End 3 - Further, the first and last lines of a loop
            (If...End If, While...Wend, For...Next)
            should line up.
    End 2
  End 1

In addition, when lines of code are really long, it makes it easier to read if you use the "Continuation Character" to split the line.

A space followed by an underscore _
tells VBA that the instruction continues _
on the next line.

For example, I would format your code as follows. I can easily tell where each loop begins and ends and I can tell which set of instructions are within each loop.

Sub TD()

Dim LSearchRow As Integer

'Start search in row 2
 LSearchRow = 2
'Loop while Column A <> ""
   While Len(Range("A" & CStr(LSearchRow)).Value) > 0
'If Column D = "DIV", copy entire row
    If Range("D" & CStr(LSearchRow)).Value = "DIV" And _
       Range("I" & CStr(LSearchRow)).Value > 0 Then
         Range("D" & CStr(LSearchRow)).EntireRow.Copy
'Insert copied Row below the original
         Range("D" & CStr(LSearchRow)).EntireRow.Insert Shift:=xlDown
'Change value in Columns I (original) and D (New)
         Range("I" & CStr(LSearchRow)) = 1
         Range("D" & CStr(LSearchRow + 1)) = "BUY"
    Else
'If Column I Column is Blank copy column J to I
       If Range("I" & CStr(LSearchRow)).Value = "" Then
            Range("I" & CStr(LSearchRow)).Value = _
            Range("I" & CStr(LSearchRow)).Offset(0, 1).Value
       End If
    End If
'Increment Search row variable
 LSearchRow = LSearchRow + 1
'Loop
   Wend
End Sub


Report •

#15
September 30, 2010 at 11:51:47
I see what you mean. Yours is easier to read by far. Thanks for all of your input DerbyDad03. It has been a valuable experience!

Report •


Ask Question