copy to same column

  • Thread starter Thread starter ray denneny
  • Start date Start date
R

ray denneny

After coping and pasteing a Word file I use Text to
Columns to parse the data. The phone numbers end up in
different columns and I want to cut and copy them into the
same column. I have tried a couple of different things but
can't get them to work. Any help will be greatly
appreciated.




Cells.Find(What:="???-???-????", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.Cut
Range("A1").Select
ActiveSheet.Paste

I use this site all the time, Thank You, Thank You, Thank
You.

ray denneny
 
I didn't know what column to put them in so I used column 1.

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myRow As Range
Dim wks As Worksheet
Dim FoundCell As Range
Dim findWhat As String

Set wks = Worksheets("sheet1")

findWhat = "???-???-????"

With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)).EntireRow
For Each myRow In myRng.Rows
With myRow
Set FoundCell = .Cells.Find(What:=findWhat, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing--didn't find it
Else
.Cells(1).Value = FoundCell.Value
End If
End With
Next myRow
End With

End Sub


I also used column A to determine the rows to look through.

You can modify this line ".cells(1).value = foundcell.value" to point at your
correct column:

..cells(26).value would be column 26 (Z).
 
Back
Top