Cut and copying cells in a macro
 

How would i go about writing an ecxel macro that would look in a cell and if
it contained something cut and paste it into the cell next to it, and if the
cell contained nothing would move to the next cell? Lets say if a1 contained
something it would cut and paste it to b1 and then it would move to a2 ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

"Neal" wrote:

How would i go about writing an ecxel macro that would look in a cell and if
it contained something cut and paste it into the cell next to it, and if the
cell contained nothing would move to the next cell? Lets say if a1 contained
something it would cut and paste it to b1 and then it would move to a2 ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell and
if
it contained something cut and paste it into the cell next to it, and if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

Try this and let me know if it works.
You need to determine the number of your last row. This macro looks in
column A for nonblanks and places that same value in column B.

Dim i As Integer
Dim LastRow As Integer

LastRow = 10

For i = 1 To LastRow
Range("A" & i).Select
If ActiveCell < "" Then ActiveCell.Offset(0, 1) = ActiveCell.Value
Next i

HTH,
Nicole


"Neal" wrote:

How would i go about writing an ecxel macro that would look in a cell and if
it contained something cut and paste it into the cell next to it, and if the
cell contained nothing would move to the next cell? Lets say if a1 contained
something it would cut and paste it to b1 and then it would move to a2 ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

It worked great , but the only thing that may be a problem is the file I
receive doesnt always have the same amount of rows. But thank you for the
help it does what I need it to do, thanks Neal.

"Nicole Seibert" wrote:

Try this and let me know if it works.
You need to determine the number of your last row. This macro looks in
column A for nonblanks and places that same value in column B.

Dim i As Integer
Dim LastRow As Integer

LastRow = 10

For i = 1 To LastRow
Range("A" & i).Select
If ActiveCell < "" Then ActiveCell.Offset(0, 1) = ActiveCell.Value
Next i

HTH,
Nicole


"Neal" wrote:

How would i go about writing an ecxel macro that would look in a cell and if
it contained something cut and paste it into the cell next to it, and if the
cell contained nothing would move to the next cell? Lets say if a1 contained
something it would cut and paste it to b1 and then it would move to a2 ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

This works great. I am assuming by the way it is written that if the file has
2 or 100 rows it will execute until finished! Thanbks for the great script,
Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell and
if
it contained something cut and paste it into the cell next to it, and if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

I also wanted to know if the text that it moves starts with -- I dont want
this in the move and if it ends in #'s I dont want the numbers either? I dont
mean to be a hog on the wisdom but if you can give me some more advice I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell and
if
it contained something cut and paste it into the cell next to it, and if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

Need some more information: When you say if there is a double dash or hyphen
and if there are numbers? Does that mean they don't always occur? Is there
any rhymn or reason to their occurance? Is there a set number of characters
in the cell?

"Neal" wrote:

I also wanted to know if the text that it moves starts with -- I dont want
this in the move and if it ends in #'s I dont want the numbers either? I dont
mean to be a hog on the wisdom but if you can give me some more advice I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell and
if
it contained something cut and paste it into the cell next to it, and if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

I actually fixed this issue by running a couple different macros and adding
them together. But another question I have is how do I reference a cell that
only has data? If the cell is blank then stop function. When i created a part
of the macro to trim some cells I scrolled down to the last cell which happen
to be h167, all the files I receive wont have 167 rows so i do I get around
this, lets say start at h2 to the last non blank cell theb stop! Is it
possible? Thanks Neal

"Nicole Seibert" wrote:

Need some more information: When you say if there is a double dash or hyphen
and if there are numbers? Does that mean they don't always occur? Is there
any rhymn or reason to their occurance? Is there a set number of characters
in the cell?

"Neal" wrote:

I also wanted to know if the text that it moves starts with -- I dont want
this in the move and if it ends in #'s I dont want the numbers either? I dont
mean to be a hog on the wisdom but if you can give me some more advice I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell and
if
it contained something cut and paste it into the cell next to it, and if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

When you say "if it ends in #'s I dont want the numbers either", does that
mean:
- you don't want ce cell to be copied
or:
- you want the ending numbers to be stripped off

Cheers,
--
AP

"Neal" a écrit dans le message de news:
...
I also wanted to know if the text that it moves starts with -- I dont want
this in the move and if it ends in #'s I dont want the numbers either? I
dont
mean to be a hog on the wisdom but if you can give me some more advice I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell
and
if
it contained something cut and paste it into the cell next to it, and
if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

I'm sorry for the confusion. I would like the ending numbers to be stripped
off. I was also wondering how I would right the same code you wrote for the
copying but instead of copying I want it to trim a selected column instead.
Thank Neal.

"Ardus Petus" wrote:

When you say "if it ends in #'s I dont want the numbers either", does that
mean:
- you don't want ce cell to be copied
or:
- you want the ending numbers to be stripped off

Cheers,
--
AP

"Neal" a écrit dans le message de news:
...
I also wanted to know if the text that it moves starts with -- I dont want
this in the move and if it ends in #'s I dont want the numbers either? I
dont
mean to be a hog on the wisdom but if you can give me some more advice I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de news:
...
How would i go about writing an ecxel macro that would look in a cell
and
if
it contained something cut and paste it into the cell next to it, and
if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to a2
ect..
Any help would be greatly appreciated, thanks Neal.

Cut and copying cells in a macro
 

This should do it.
Before you run the macro, in VBE:
ToolsReferences tick "Microsoft VBScript Regukar Expressions 1.0"

'--------------
Sub MoveIt()
Dim rCell As Range
Dim re As RegExp
Set re = New RegExp
re.Pattern = "\d+$"

For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))

With rCell
If .Value < "" And Left(.Value, 2) < "--" Then
.Offset(0, 1).Value = re.Replace(.Value, "")
End If
End With
Next rCell
End Sub
'---------------

HTH
--
AP

"Neal" a écrit dans le message de news:
...
I'm sorry for the confusion. I would like the ending numbers to be
stripped
off. I was also wondering how I would right the same code you wrote for
the
copying but instead of copying I want it to trim a selected column
instead.
Thank Neal.

"Ardus Petus" wrote:

When you say "if it ends in #'s I dont want the numbers either", does
that
mean:
- you don't want ce cell to be copied
or:
- you want the ending numbers to be stripped off

Cheers,
--
AP

"Neal" a écrit dans le message de news:
...
I also wanted to know if the text that it moves starts with -- I dont
want
this in the move and if it ends in #'s I dont want the numbers either?
I
dont
mean to be a hog on the wisdom but if you can give me some more advice
I
would greatl appreciate it, Neal.

"Ardus Petus" wrote:

Hi Neal,

Sub MoveIt()
Dim rCell As Range
For Each rCell In Range( _
Range("A1"), _
Cells(Rows.Count, "A").End(xlUp))
If rCell.Value < "" Then
rCell.Copy rCell.Offset(0, 1)
End If
Next rCell
End Sub

HTH
--
AP

"Neal" a écrit dans le message de
news:
...
How would i go about writing an ecxel macro that would look in a
cell
and
if
it contained something cut and paste it into the cell next to it,
and
if
the
cell contained nothing would move to the next cell? Lets say if a1
contained
something it would cut and paste it to b1 and then it would move to
a2
ect..
Any help would be greatly appreciated, thanks Neal.