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

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
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


 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to the board.
Untested, but is this what you're after?
Code:
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

New Member
Joined
Dec 9, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows
Awsome! That works perfectly. Thanks so much for your Help, I can work out what some of it does too.
Ed
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Ed Harris

New Member
Joined
Dec 9, 2017
Messages
20
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,755
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top