Macro to Move Data

[NANCY SKYES]

Hi


I have data located in 1 row across 4 columns in many different ranges, an example is:


cell C38 cell D38 cell E38 cell F38

10-18 1.00 2.00 3.00



This is only my second post and I think this is possible so here goes. I was looking for help to develop a macro that:


1) [FONT=Liberation Serif, Times New Roman, serif]Searches about 20 different ranges, C[/FONT]<code class="western">[FONT=Liberation Serif, Times New Roman, serif]20:C50,P20:P50,Z20:Z50,AE20:AE50 [/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif]with the same start line of 20 and the same end line of 50[/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif] (not all listed here, need macro to be hard coded to add/delete as necessary also, [/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif]I[/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif] have data above and below [/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif]all the [/FONT]</code><code class="western">[FONT=Liberation Serif, Times New Roman, serif]search ranges)[/FONT]</code>[FONT=Liberation Serif, Times New Roman, serif] [/FONT][FONT=Liberation Serif, Times New Roman, serif]for sets of numbers (such as 10-10, 20-1 etc). The numbers are always separated by a dash. Most cells in the ranges will be blank but there will always be data (a set of numbers) in at least 2 [/FONT][FONT=Liberation Serif, Times New Roman, serif]of the [/FONT][FONT=Liberation Serif, Times New Roman, serif]ranges. Other ranges may [/FONT][FONT=Liberation Serif, Times New Roman, serif]or may not[/FONT][FONT=Liberation Serif, Times New Roman, serif] be [/FONT][FONT=Liberation Serif, Times New Roman, serif]blank[/FONT][FONT=Liberation Serif, Times New Roman, serif]. [/FONT]


2) When the code finds a set of numbers I need it to move that set of numbers down to the cell directly below it. The issue here is if there is a set of numbers already in that cell that set of numbers must be moved first down by one cell. The issue is no cell should be overwritten by new data from the above cell. Also, I need the code to delete the contents in the three cells to the right of the found set of numbers (above that would be delete cells D38/E38/F38). The data in the 3 cells deleted is written by another macro I use.


3) The next request is that when the set of numbers gets moved down that only the 2^nd set of numbers would be increased by one (10-1 would become 10-2). Please see my sample data below.


4) I would also need the code to search for sets of numbers that have a 20 in the second number and move that set of numbers by two cells down and increase the last number by one. There is an empty row below all the sets of numbers that reach 20. An example would be 2-20 would become 2-21 two cells down from where it was found.


Additional information


- The sheet name is Sheet 1 and their is only one sheet in the workbook.
- Using Excel 2007.
- The search range cells are formatted as text.
- The write cells are formatted as general.


Sample data:


1) The code searches [FONT=Liberation Serif, Times New Roman, serif]C[/FONT]<code class="western">[FONT=Liberation Serif, Times New Roman, serif]20:C50,P20:P50,Z20:Z50,AE20:AE50[/FONT]</code>. The first found set of numbers is 10-18 in cell C38. The code would check the cell contents for any existing data first and then move the data in cell C38 to cell C39 and increase the last number by 1. The code would also clear contents in cells D38/E38/F38. The expected result is 10-39 in cell C39.


2) The second found set of numbers would be 9-20 in cell P40. The code would write 9-21 in cell P42, moving two cells down because of the last number being 20. The code would also clear contents in cells Q38/R38/S38. The expected result is 9-42 in cell P42.


I hope I’m clear but if you have any questions please ask so I can clarify any issue(s).


Thanks so much for all your help.




<style type="text/css">p { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "SimSun"; font-size: 12pt; }p.ctl { font-family: "Mangal"; font-size: 12pt; }code.western { font-family: "Liberation Mono", "Courier New", monospace; }code.cjk { font-family: "WenQuanYi Micro Hei Mono", monospace; }code.ctl { font-family: "Liberation Mono", "Courier New", monospace; }a:link { }</style>

 

[DanteAmor]

Hi Nancy:

Help me with this:

Sample data:


1) The code searches C<code class="western">20:C50,P20:P50,Z20:Z50,AE20:AE50</code>. The first found set of numbers is 10-18 in cell C38.

Only the first set of numbers per column is the one that moves?

The code would check the cell contents for any existing data first and then move the data in cell C38 to cell C39 and increase the last number by 1. The code would also clear contents in cells D38/E38/F38. The expected result is 10-39 or 10-19 in cell C39.

That is, if in cell C38 you have 10-19 (process), but if in cell C49 there is another set, is it also processed?

"and increase the last number by one"

2) The second found set of numbers would be 9-20 in cell P40. The code would write 9-21 in cell P42, moving two cells down because of the last number being 20. The code would also clear contents in cells Q38/R38/S38. The expected result is 9-42 or 9-21 in cell P42.



<style type="text/css">p { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "SimSun"; font-size: 12pt; }p.ctl { font-family: "Mangal"; font-size: 12pt; }code.western { font-family: "Liberation Mono", "Courier New", monospace; }code.cjk { font-family: "WenQuanYi Micro Hei Mono", monospace; }code.ctl { font-family: "Liberation Mono", "Courier New", monospace; }a:link { }</style>

It would be very useful, if you show a table with all the possible scenarios.


You say "set of numbers must be moved first down by one cell", but if the set is in cell C50, can it be moved to C51?

 

[NANCY SKYES]

Hi Dante

I copied your question with the answer in yellow below it.​

1) The code searches [FONT=Liberation Serif, Times New Roman]C[/FONT]<code class="western">[FONT=Liberation Serif, Times New Roman]20:C50,P20:P50,Z20:Z50,AE20:AE50[/FONT]</code>. The first found set of numbers is 10-18 in cell C38.

Only the first set of numbers per column is the one that moves?​

Answer: All found sets of numbers move down 1 cell, unless the last number is a 20 in the set then it would be moved down 2 cells (eg. 1-20).

2) The code would check the cell contents for any existing data first and then move the data in cell C38 to cell C39 and increase the last number by 1. The code would also clear contents in cells D38/E38/F38. The expected result is 10-39 or 10-19 in cell C39.

That is, if in cell C38 you have 10-19 (process), but if in cell C49 there is another set, is it also processed?

"and increase the last number by one"​

Answer: All sets of numbers are increased by one number. I wasn’t sure how to write this up. There must be a term for this. Here is an example of multiple line that have to be moved:


cell Z25 has 5-5 cell AA25 HAS DATA cell AB25 HAS DATA cell AC25 HAS DATA
cell Z26 has 6-6 cell AA26 HAS DATA cell AB26 HAS DATA cell AC26 HAS DATA
cell Z27 has 7-7 cell AA27 HAS DATA cell AB27 HAS DATA cell AC27 HAS DATA


All 3 cells Z25, Z26, Z27 has to be moved down by one cell, the last number has to be increased by 1 and the data in all 3 cells to the right of each one has to cleared and not replaced with any data. How does the code know to move cell Z27 down one first then Z26 down one and finally Z25 down one?


The expected result would be:


cell Z26 has 5-6 cell AA26 blank cell AB26 blank cell AC26 blank
cell Z27 has 6-7 cell AA27 blank cell AB27 blank cell AC27 blank
cell Z28 has 7-8 cell AA28 blank cell AB28 blank cell AC28 blank

3) The second found set of numbers would be 9-20 in cell P40. The code would write 9-21 in cell P42, moving two cells down because of the last number being 20. The code would also clear contents in cells Q38/R38/S38. The expected result is 9-42 or 9-21 in cell P42.

Answer: I can’t believe this I read this posting I don’t know how many times and here is a mistake. I had it correct at the “The code would write 9-21 in cell P42”.​

4) You say "set of numbers must be moved first down by one cell", but if the set is in cell C50, can it be moved to C51?​

Answer: When the numbers are written to row 50 they are deleted so there is no C51 option. I was not clear in that one, very sorry about that.




I really tried so hard to make this posting clear and easy to understand. I really hope that I have cleared up any misunderstanding, if please let me know. I very sorry for the hassle and truly appreciate your help.
<style type="text/css">blockquote { direction: ltr; color: rgb(0, 0, 0); }blockquote.western { font-family: "Times New Roman", serif; font-size: 12pt; }blockquote.cjk { font-family: "SimSun"; font-size: 12pt; }blockquote.ctl { font-family: "Mangal"; font-size: 12pt; }p { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "SimSun"; font-size: 12pt; }p.ctl { font-family: "Mangal"; font-size: 12pt; }code.western { font-family: "Liberation Mono", "Courier New", monospace; }code.cjk { font-family: "WenQuanYi Micro Hei Mono", monospace; }code.ctl { font-family: "Liberation Mono", "Courier New", monospace; }a:link { }</style>

 

[DanteAmor]

This was very complicated, it has nothing to do with your first thread.

The biggest problem is when the number is 20 and must moving two cells down.
I did several tests, maybe I did not contemplate all possible cases, so you should try and tell me if there is a problem.

--------------------
Another detail is that it is not clear which cells should be cleaned, for your examples:

ej1. The first found set of numbers is 10-18 in cell C38 ... The code would also clear contents in cells D38/E38/F38.

ej2. cell Z25 has 5-5 ... The expected result would be: ... cell AA26 blank

If you review your example, in ej1, clean the cell in the same row.
In ej2, clean the cell in the next row.



--------------------
For the moment. The macro cleans the cell in the next row.
--------------------

Sub MoveData()
  Dim rng As Range, c As Range, nums As Variant, nvo As Variant, a() As Variant, dato As String
  Dim i As Long, j As Long, col As Long, ini As Long, fin As Long, nfin As Long, nini As Long
  Dim k As Long, hay As Long
  Set rng = Range("[COLOR=#0000ff]C20:C50,P20:P50,Z20:Z50[/COLOR]")
  
  For Each c In rng.Columns
    ini = c.Cells(1, 1).Row
    nini = ini - 1
    fin = c.Rows.Count + ini - 2
    nfin = fin + 1
    col = c.Cells(1, 1).Column
    Cells(nfin, col).Value = ""
    a = c.Value
    For i = fin To ini Step -1
      dato = a(i - nini, 1)
      If InStr(1, dato, "-") > 0 Then
        nums = Split(dato, "-")
        nvo = nums(0) & "-" & nums(1) + 1
        If Val(nums(1)) = 20 Then
          hay = False
          For k = i + 2 To fin + 1
            If Cells(k, col).Value = "" Then
              Cells(k, col).Cut
              Cells(i + 1, col).Insert Shift:=xlDown
              Cells(i + 2, col).Value = nvo
              Cells(i + 2, col).Offset(0, 1).Resize(1, 3).Value = ""
              nfin = nfin - 1
              Cells(i, col).Value = ""
              hay = True
              Exit For
            Else
              
            End If
          Next k
          If hay = False Then
            If i = fin - 1 Then
              Cells(i + 2, col).Value = nvo
              Cells(i + 2, col).Offset(0, 1).Resize(1, 3).Value = ""
              nfin = nfin - 1
              Cells(i, col).Value = ""
              Cells(i + 1, col).Value = ""
            Else
              If i = fin Then
                Cells(i, col).Value = ""
              End If
            End If
          End If
        Else
          Cells(i + 1, col).Value = nvo
          Cells(i + 1, col).Offset(0, 1).Resize(1, 3).Value = ""
          nfin = nfin - 1
          Cells(i, col).Value = ""
        End If
      End If
    Next i
  Next c
End Sub

 

[NANCY SKYES]

Wow, this looks really impressive! I'm off to visit my sister this weekend but will test the code on Tuesday. Thank-you so much and have a great weekend.

 

[DanteAmor]

Greetings to your sister and also have a good weekend!

 

[NANCY SKYES]

Hi Dante, I’m back


I just finished testing your macro and it works perfectly. The only issue is it’s not clearing the contents of the 3 cells to the right of set of numbers with 20 (7-20, 1-20 etc) as the last number after the set of numbers get moved down two cells. I really hope this is a small addition to the macro.

Thank-you again.


<style type="text/css">p { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "SimSun"; font-size: 12pt; }p.ctl { font-family: "Mangal"; font-size: 12pt; }a:link { }</style>

 

[DanteAmor]

Hi Dante, I’m back
I just finished testing your macro and it works perfectly. The only issue is it’s not clearing the contents of the 3 cells to the right of set of numbers with 20 (7-20, 1-20 etc) as the last number after the set of numbers get moved down two cells. I really hope this is a small addition to the macro.
Thank-you again.

You read my note:

Another detail is that it is not clear which cells should be cleaned, for your examples:

ej1. The first found set of numbers is 10-18 in cell C38 ... The code would also clear contents in cells D38/E38/F38.

ej2. cell Z25 has 5-5 ... The expected result would be: ... cell AA26 blank

If you review your example, in ej1, clean the cell in the same row.
In ej2, clean the cell in the next row.

 

[DanteAmor]

I really hope this is a small addition to the macro.

Apparently it is.
try this

Sub MoveData()
  Dim rng As Range, c As Range, nums As Variant, nvo As Variant, a() As Variant, dato As String
  Dim i As Long, j As Long, col As Long, ini As Long, fin As Long, nfin As Long, nini As Long
  Dim k As Long, hay As Long
  Set rng = Range("C20:C50,P20:P50,Z20:Z50")
  DoEvents
  For Each c In rng.Columns
    ini = c.Cells(1, 1).Row
    nini = ini - 1
    fin = c.Rows.Count + ini - 2
    nfin = fin + 1
    col = c.Cells(1, 1).Column
    Cells(nfin, col).Value = ""
    a = c.Value
    For i = fin To ini Step -1
      dato = a(i - nini, 1)
      If InStr(1, dato, "-") > 0 Then
        nums = Split(dato, "-")
        nvo = nums(0) & "-" & nums(1) + 1
        If Val(nums(1)) = 20 Then
          hay = False
          For k = i + 2 To fin + 1
            If Cells(k, col).Value = "" Then
              Cells(k, col).Cut
              Cells(i + 1, col).Insert Shift:=xlDown
              Cells(i + 2, col).Value = nvo
              Cells(i + 2, col).Offset([COLOR=#ff0000]-1[/COLOR], 1).Resize(1, 3).Value = ""
              nfin = nfin - 1
              Cells(i, col).Value = ""
              hay = True
              Exit For
            Else
              
            End If
          Next k
          If hay = False Then
            If i = fin - 1 Then
              Cells(i + 2, col).Value = nvo
              Cells(i + 2, col).Offset([COLOR=#ff0000]-1[/COLOR], 1).Resize(1, 3).Value = ""
              nfin = nfin - 1
              Cells(i, col).Value = ""
              Cells(i + 1, col).Value = ""
            Else
              If i = fin Then
                Cells(i, col).Value = ""
              End If
            End If
          End If
        Else
          Cells(i + 1, col).Value = nvo
          Cells(i + 1, col).Offset([COLOR=#ff0000]-1[/COLOR], 1).Resize(1, 3).Value = ""
          nfin = nfin - 1
          Cells(i, col).Value = ""
        End If
      End If
    Next i
  Next c
End Sub

 

[NANCY SKYES]

Thanks, the change to the code does NOW clear the cells to the right of them with data such 1-5 or 10-4 but the data with a 20 at the end (20-20) does not clear the 3 cells to the right of it. Always sorry to trouble you.


Thanks
Nancy
<style type="text/css">p { margin-bottom: 0.21cm; direction: ltr; color: rgb(0, 0, 0); }p.western { font-family: "Times New Roman", serif; font-size: 12pt; }p.cjk { font-family: "SimSun"; font-size: 12pt; }p.ctl { font-family: "Mangal"; font-size: 12pt; }a:link { }</style>