Move data based on criteria

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi all,

I am trying to write a macro that will move data from one cell to another, based on specific criteria.

I have data in columns A and B. Column A contains either the number 2 or 3. The macro goes down column A, and if it finds a 2, it should move the contents of the same row and column B into column C. And if it finds a 3 in column A, it should move the contents of the same row and column B into column D.

However, the code that I've come up with (from info online etc), moves the data from column A, not column B.

This is what I have so far:
Sub Movetest()
Dim r As Range
For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
If r.Value Like "[2]" Then
r.Cut r.Offset(-1, 3)
End If
If r.Value Like "[3]" Then
r.Cut r.Offset(-1, 4)
End If
Next
MsgBox "done"
End Sub

I also attach a small screenshot to indicate what the data looks like. I hope I've explained it well enough for someone to be able to help.

If anyone can help, I will be extremely greatful.
 

Attachments

  • test.png
    test.png
    11.6 KB · Views: 2

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Your code, description & image all seem to conflict, so entirely sure what you need.
Are you trying to move all odd rows (ie 1,3,5,7 etc) to col D & all even rows to col C?
 

minette

Board Regular
Joined
Jul 8, 2005
Messages
237
Hi, sorry no. I didn't explain it very well. Column A contains 2's and 3's. If there's a 2 in the cell, it should move the contents next to it in column B to a row higher and to column D. And if there's a 3 in the cell, it should move the contents next to it in column B to a row higher and to column E. Hope it makes more sense.

Here's a screenshot which will hopefully explain better.... thanks.
 

Attachments

  • tes2.png
    tes2.png
    245.7 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
Ok, in that case all you have to do is change the two lines where you cut the data to be like
VBA Code:
r.Offset(, 1).Cut r.Offset(-1, 3)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,993
Messages
5,622,049
Members
415,875
Latest member
Tarali

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