VBA cutting from a fiel and pasting to the line above and after

[Knovi]

Hi All, I`m looking trying to code my script for Excel,
I have a Data set that looks like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



What I want to do is go through all my data. If I find something called Notes Cut this and the field next to it(hello) and paste it at the end of Dollar amount from the previous line.

Sub test1()
Dim rcnt As Long
rcnt = Worksheets("Feuil1").Range("C" & Rows.Count).End(xlUp).Row


For i = 1 To rcnt


If Range("C" & i).Value = "Notes" Then
    Range("C" & i, "D" & i).Cut
    Range("M" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
      
   
   End If
Next i


Application.CutCopyMode = False
End Sub

 

[Rick Rothstein]

Hi All, I`m looking trying to code my script for Excel,
I have a Data set that looks like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I want to do is go through all my data. If I find something called Notes Cut this and the field next to it(hello) and paste it at the end of Dollar amount from the previous line.

I think is would be helpful if you posted the final result that you want for the example above.

 

[mumps]

Try:

Sub test1()
    Dim rcnt As Long
    rcnt = Worksheets("Feuil1").Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To rcnt
        If Range("C" & i).Value = "Notes" Then
            Range("C" & i & ":D" & i).Cut Range("M" & i - 1)
        End If
    Next i
    Application.CutCopyMode = False
End Sub

 

[Knovi]

Very true.
Before
[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


After

[TABLE="width: 500"]
<tbody>[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Country[/TD]
[TD]City[/TD]
[TD]Dollar[/TD]
[TD]Notes[/TD]
[TD]Hello[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is in a report that will keep the same formating every month, it has over 1500 lines