Cut and Paste Macro

[jbvinny]

I need a piece of code that will scan column B. If the value in column B differs from the row above it, I need to paste the value from Column A into Column C in the row that the first listed value in Column B appeared.

I have a feeling I need to Dim variables but I am still learning VBA and no profficient enough just yet.

I know this is hard to explain. I would post a screen shot by my company doesnt allow me to download the html maker.

I appreciate any help!

 

[Datsmart]

This works on my test workbook:

Sub TestABC()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("B2:B" & Range("B65536").End(xlUp).Row)
    For Each c In Rng
        If c.Value <> c.Offset(-1, 0).Value Then
            c.Offset(, 1).Value = c.Offset(, -1).Value
        End If
    Next c
End Sub

 

[jbvinny]

Thanks! I was able to modify the code to work as needed. However, I need to delete the row in which the data was copied from after.....any advice?

 

[Datsmart]

It doesn't make any sense to Delete a ROW that you just copied data to.

Do you mean to just delete the contents of the CELL that was copied?

You can do that by adding a line to the code after the copy line.

Sub TestABC()
    Dim Rng As Range
    Dim c As Range
    Set Rng = Range("B2:B" & Range("B65536").End(xlUp).Row)
    For Each c In Rng
        If c.Value <> c.Offset(-1, 0).Value Then
            c.Offset(, 1).Value = c.Offset(, -1).Value
            c.Offset(, -1).ClearContents
        End If
    Next c
    Application.Run ("'" & Wkb.Name & "'!TrendMacro")
End Sub

 

[jbvinny]

You were correct on the deleting of the rows.

I modified your code to:
___________________________________________________

Sub TestABC()
Dim Rng As Range
Dim c As Range
Set Rng = Range("A2:A" & Range("A65536").End(xlUp).Row)
For Each c In Rng
If c.Value = c.Offset(-1, 0).Value Then
c.Offset(-1, 2).Value = c.Offset(0, 1).Value
End If
Next c
End Sub
________________________________________________

After testing it works for the most part. Except when there are more than two occurances in "A". Currently the code scans Column "A" for duplicates. Then copies the data from Column "B" into "C" of the first Row of that occurence. However, if there are three records with the same data in Column "A" I would want to copy the third row of data from "B" and place it in "D". The forth would be copied from "B" and pasted into "E" and so on.....

I realize this is asking a lot but if you can get me started I could do the leg work.

Thanks for all the help!

 

[Datsmart]

Sorry for the delay, other obligations....

Give this a try:

Sub TestABCD()
Dim i As Integer
lr = Range("A65536").End(xlUp).Row
For i = 2 To lr
    cnt = 0
    If Cells(i, 1).Value = Cells(i + 1, 1).Value Then
        Cells(i, "C").Value = Cells(i, "B").Value
        Cells(i, "B").Value = ""
        cnt = cnt + 1
    If Cells(i, 1).Value = Cells(i + cnt, 1).Value Then
        Cells(i + 1, "D").Value = Cells(i + 1, "B").Value
        Cells(i + 1, "B").Value = ""
        cnt = cnt + 1
    If Cells(i, 1).Value = Cells(i + cnt, 1).Value Then
        Cells(i + cnt, "E").Value = Cells(i + cnt, "B").Value
        Cells(i + cnt, "B").Value = ""
        cnt = cnt + 1
    If Cells(i, 1).Value = Cells(i + cnt, 1).Value Then
        Cells(i + cnt, "F").Value = Cells(i + cnt, "B").Value
        Cells(i + cnt, "B").Value = ""
        cnt = cnt + 1
    If Cells(i, 1).Value = Cells(i + cnt, 1).Value Then
        Cells(i + cnt, "G").Value = Cells(i + cnt, "B").Value
        Cells(i + cnt, "B").Value = ""
        cnt = cnt + 1
    End If
    End If
    End If
    End If
    i = i + cnt
    End If
Next i
End Sub