How to cut and paste to new column based on data in another column

[Ed Harris]

Hello,

I saw this post by DK340;
I need a macro that will loops through each value in column (K:K) and if the value, for example K1, is equal to "Credit Card", it needs to find the value in column B of the same row (in this example B1) and copies this value and then pastes this into cell A19. As it goes down through column K and finds more instances of "Credit Card" it pastes the associated data from column B in the A20,A21,A22 etc...until all the data in column B that is associated with "Credit Card" is in column A.

and solution by Kevin Lazell;

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
y = 19: Z = 5000
Do
x = x + 1
If Range("k" & x) = "credit card" Then Range("c" & y) = Range("b" & x): y = y + 1
If x = Z Then Exit Sub
Loop
End Sub

and tried to adapt it to my use by pasting it into a recorded macro and editing it(since I couldnt find out how to write and save a macro from scratch, being a total newb);

Sub Macro2()
'
' Macro2 Macro
' move data
'


'
y = 2: Z = 5000
Do
x = x + 1
If Range("j" & x) = "1" Then Range("r" & y) = Range("q" & x): y = y + 1
If x = Z Then Exit Sub
Loop
End Sub

My data is a series of ones and zeros in column J, for each row with a one in column J I want to cut and paste the data from column Q to column R of the same row so that I can make a chart of the data.

I substituted in range J for "K" and r for "credit card" then the third range with q
Needles to say it didnt work, and anyway it was copy not cut and paste and I got all rows copied not just the ones with 1 in them.
any help most gratefully accepted
Ed

 

[Fluff]

Hi & welcome to the board.
Untested, but is this what you're after?

Sub Macro2()

   Dim Cnt As Long
   
   For Cnt = 1 To Range("J" & Rows.Count).End(xlUp).row
      If Range("J" & Cnt).Value = 1 Then
         Range("R" & Cnt).Value = Range("Q" & Cnt).Value
         Range("Q" & Cnt).ClearContents
      End If
   Next Cnt
End Sub

 

[Ed Harris]

Awsome! That works perfectly. Thanks so much for your Help, I can work out what some of it does too.
Ed

 

[Fluff]

Glad to help & thanks for the feedback

 

[Ed Harris]

Actually is there a way to specify the range that it will operate on? Because, if I add more data to the sheet and run the macro again it operates on the empty cells that have already been processed and deletes or overwrites the new cells with nothing since the source has been moved?
Ed

 

[Fluff]

How about

Sub Macro2()

   Dim Cnt As Long
   
   For Cnt = 1 To Range("J" & Rows.Count).End(xlUp).row
      If Range("J" & Cnt).Value = 1 And Len(Range("Q" & Cnt)) > 0 Then
         Range("R" & Cnt).Value = Range("Q" & Cnt).Value
         Range("Q" & Cnt).ClearContents
      End If
   Next Cnt
End Sub

If Col Q is empty it will ignore that row & move on to the next

 

[Ed Harris]

Yes, that works perfectly

Thanks
Ed

 

[Fluff]

Glad to help & thanks for the feedback