Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to same column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy to same column
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). ray denneny wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I copy row height like i copy column width? | Excel Discussion (Misc queries) | |||
How to search column, copy row, and copy to another sheet in same | Excel Discussion (Misc queries) | |||
how to make one column copy from one sheet to anoth column w/o zer | Excel Discussion (Misc queries) | |||
how to copy contents of one column to another column in another worksheet | Excel Discussion (Misc queries) | |||
To copy values in a column relevant to text in an adjacent column? | Excel Worksheet Functions |