How to move cell contents using VBA

[Legacy 431720]

I’m using a program that I’m trying to add to. I need to move the contents down one cell but I’m not sure how does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down. I’ve included a jpeg of my excel sheet.
I hope you can help.



I'm currently using the program below.

<code class="western">Sub do_it()</code>

<code class="western"> </code><code class="western">Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range</code>

<code class="western"> </code><code class="western">Set sht = ActiveSheet</code>

<code class="western"> </code><code class="western">n = sht.Range("A1")</code>

<code class="western"> </code><code class="western">For Each cell In sht.Range("D1:D12,A16:A31,D16:D31,G16:G31,J16:J31,M16:M31").Cells</code>

<code class="western"> </code><code class="western">tmp = cell.Offset(0, 1).Value</code>

<code class="western"> </code><code class="western">If cell.Value = n And tmp Like "*#-#*" Then</code>

<code class="western"> </code><code class="western">'get the first number</code>
<code class="western"> </code><code class="western">num = CLng(Trim(Split(tmp, "-")(0)))</code>
<code class="western"> </code><code class="western">Debug.Print "Found a positive result in " & cell.Address</code>

<code class="western"> </code><code class="western">'find the next empty cell in the appropriate row</code>
<code class="western"> </code><code class="western">Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)</code>
<code class="western"> </code><code class="western">'make sure not to add before col L</code>
<code class="western"> </code><code class="western">If rngDest.Column < 12 Then Set rngDest = sht.Cells(num, 12)</code>

<code class="western"> </code><code class="western">cell.Offset(0, 1).Copy rngDest</code>
<code class="western"> </code><code class="western">Exit For</code>

<code class="western"> </code><code class="western">End If</code>
<code class="western"> </code><code class="western">Next</code>

<code class="western">End Sub</code> I'm trying to have the program move all the number sets down in the column E1:E12.
I need the program to move the set of numbers (could be more than one set of numbers in the range) down to the following cell below it and increase the last number in the set (no limit to the count). So in the example of cell E1 (8-16) would move to E2 and become 8-17 (cell E1 would be blank when after the move). When sets of number are located in cell E12 they would move to E1 but still increase the last number and would go round and round (E1 to E12 and back). How does the program move contents down from one cell to the next if the next cell is full without overwriting it and then move that one down, if this makes sense. An example is cell E12, how does it move up to E1 if E1 is full but has to be moved? That’s it but it must be done using the above program.
Thanks so much for any help.

 

[PhilS2520]

Hi Mike Guest,

Wow! The image did not come through and made a mess.

I would suggest that you step into the code line by line and figure it out.

To step into the code you will place the cursor somewhere in the code you want to execute, and press F8 and the code will execute line by line.

If this is NOT the code that you wrote, you must be VERY CAREFUL not to break it.

Good luck.

 

[Legacy 421546]

Your declarations (Dim) are not acting the way you think:
http://learnexcelmacro.com/wp/2012/10/declaring-multiple-variables-with-one-dim-statement/

Also read this:
https://stackoverflow.com/help/mcve