Cut and copying cells in a macro

[Guest]

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.

 

[Guest]

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.

 

[Ardus Petus]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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

 

[Ardus Petus]

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,

 

[Guest]

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]

This should do it.
Before you run the macro, in VBE:
Tools>References 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