Macro moving cells

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
Sample data:

1234567-123
abcde | fghijk

to

1234567-123 | abcde | fghijk


If A1 = 1234567-123 Then
move A2 and B2 to B1 and C1

For the whole column A

A1 will always be numeric format as shown.

Thanks.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
something like
Code:
Sub test()
Dim r As Range
For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
   If r.Text = "1234567-123" Then r.Offset(,1).Value = r.Offset(1).Value
Next
End Sub
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
Sorry, maybe I didn't make it clear enough. The numeric "1234567-123" can be any other number throughout column A.

eg:
0123456-456
7894561-789
9876543-987
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Sorry, maybe I didn't make it clear enough. The numeric "1234567-123" can be any other number throughout column A.

eg:
0123456-456
7894561-789
9876543-987

Are the cells formated as "0000000-000" ?
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

Sorry, maybe I didn't make it clear enough. The numeric "1234567-123" can be any other number throughout column A.

eg:
0123456-456
7894561-789
9876543-987

Are the cells formated as "0000000-000" ?

Yes.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Sub sample()
Dim r As Range
With CreateObject("VBScript.RegExp")
    .Pattern = "^\d{7}-\d{3}$"
    For Each r In Range("a1",Range(2a" & Rows.Count).End(xlUp))
         If .test(r.Text) Then r.Offset(,1).Value = r.Offset(1).Value
    Next
End With
End Sub
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

try
Code:
Sub sample()
Dim r As Range
With CreateObject("VBScript.RegExp")
    .Pattern = "^\d{7}-\d{3}$"
    For Each r In Range("a1",Range("a" & Rows.Count).End(xlUp))
         If .test(r.Text) Then r.Offset(,1).Value = r.Offset(1).Value
    Next
End With
End Sub

result:

1234567-123
abcde | fghijk

to

1234567-123 | abcde

'fghijk' is not moved.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Ah...
Code:
Sub sample()
Dim r As Range
With CreateObject("VBScript.RegExp")
    .Pattern = "^\d{7}-\d{3}$"
    For Each r In Range("a1",Range(2a" & Rows.Count).End(xlUp))
         If .test(r.Text) Then r.Offset(,1).Resize(,2).Value = r.Offset(1).Resize(,2).Value
    Next
End With
End Sub
 

Forum statistics

Threads
1,136,350
Messages
5,675,246
Members
419,557
Latest member
razlevav

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