Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy only certain text
Column A has 3,500 rows with lots of excess text,below is
some example text. I need any row that says "MAC Address = " and the two rows above it, copied onto sheet 2. Basically the only information I need is, the MAC Address and Username all other text is useless. Thanks, Melissa Ex. H:\nbtstat -a 10.22.23.22 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001839 NADSUSEA WDMECH001839 WDMECH001839 WDMECH001839$ NADSUSEA JOANNE.EMELOP MAC Address = 00-08-74-CB-A7-96 H:\nbtstat -a 10.22.23.23 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001384 NADSUSEA NADSUSEA WDMECH001384 WDMECH001384 JOSEPH.RESSLER MAC Address = 00-08-74-C9-9E-D0 H:\nbtstat -a 10.22.23.24 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy only certain text
Sub CopyData()
Dim rng as Range, rw as Long, fAddr as String rw = 1 set rng = cells.Find("MAC Address", Lookat:=xlpart) if not rng is nothing then fAddr = rng.Address do rng.offset(-2,0).Resize(3).copy _ destination:=worksheets("Sheet2").Cells(rw,1) rw = rw + 3 set rng = cells.FindNext(rng) while rng.Address < fAddr End if End Sub -- Regards, Tom Ogilvy "Melissa" wrote in message ... Column A has 3,500 rows with lots of excess text,below is some example text. I need any row that says "MAC Address = " and the two rows above it, copied onto sheet 2. Basically the only information I need is, the MAC Address and Username all other text is useless. Thanks, Melissa Ex. H:\nbtstat -a 10.22.23.22 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001839 NADSUSEA WDMECH001839 WDMECH001839 WDMECH001839$ NADSUSEA JOANNE.EMELOP MAC Address = 00-08-74-CB-A7-96 H:\nbtstat -a 10.22.23.23 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001384 NADSUSEA NADSUSEA WDMECH001384 WDMECH001384 JOSEPH.RESSLER MAC Address = 00-08-74-C9-9E-D0 H:\nbtstat -a 10.22.23.24 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy only certain text
I am getting the Microsoft Visual Basica error:
Compile error: End If without block If I am positive I type it out correctly... Melissa -----Original Message----- Sub CopyData() Dim rng as Range, rw as Long, fAddr as String rw = 1 set rng = cells.Find("MAC Address", Lookat:=xlpart) if not rng is nothing then fAddr = rng.Address do rng.offset(-2,0).Resize(3).copy _ destination:=worksheets("Sheet2").Cells(rw,1) rw = rw + 3 set rng = cells.FindNext(rng) while rng.Address < fAddr End if End Sub -- Regards, Tom Ogilvy "Melissa" wrote in message ... Column A has 3,500 rows with lots of excess text,below is some example text. I need any row that says "MAC Address = " and the two rows above it, copied onto sheet 2. Basically the only information I need is, the MAC Address and Username all other text is useless. Thanks, Melissa Ex. H:\nbtstat -a 10.22.23.22 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001839 NADSUSEA WDMECH001839 WDMECH001839 WDMECH001839$ NADSUSEA JOANNE.EMELOP MAC Address = 00-08-74-CB-A7-96 H:\nbtstat -a 10.22.23.23 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001384 NADSUSEA NADSUSEA WDMECH001384 WDMECH001384 JOSEPH.RESSLER MAC Address = 00-08-74-C9-9E-D0 H:\nbtstat -a 10.22.23.24 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy only certain text
The keywork Loop got left out.
Sub CopyData() Dim rng As Range, rw As Long, fAddr As String rw = 1 Set rng = Cells.Find("MAC Address", Lookat:=xlPart) If Not rng Is Nothing Then fAddr = rng.Address Do rng.Offset(-2, 0).Resize(3).Copy _ Destination:=Worksheets("Sheet2").Cells(rw, 1) rw = rw + 3 Set rng = Cells.FindNext(rng) Loop While rng.Address < fAddr End If End Sub -- Regards, Tom Ogilvy "Melissa" wrote in message ... I am getting the Microsoft Visual Basica error: Compile error: End If without block If I am positive I type it out correctly... Melissa -----Original Message----- Sub CopyData() Dim rng as Range, rw as Long, fAddr as String rw = 1 set rng = cells.Find("MAC Address", Lookat:=xlpart) if not rng is nothing then fAddr = rng.Address do rng.offset(-2,0).Resize(3).copy _ destination:=worksheets("Sheet2").Cells(rw,1) rw = rw + 3 set rng = cells.FindNext(rng) while rng.Address < fAddr End if End Sub -- Regards, Tom Ogilvy "Melissa" wrote in message ... Column A has 3,500 rows with lots of excess text,below is some example text. I need any row that says "MAC Address = " and the two rows above it, copied onto sheet 2. Basically the only information I need is, the MAC Address and Username all other text is useless. Thanks, Melissa Ex. H:\nbtstat -a 10.22.23.22 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001839 NADSUSEA WDMECH001839 WDMECH001839 WDMECH001839$ NADSUSEA JOANNE.EMELOP MAC Address = 00-08-74-CB-A7-96 H:\nbtstat -a 10.22.23.23 Local Area Connection: Node IpAddress: [10.22.2.193] Scope Id: [] NetBIOS Remote Machine Name Table Name Type Status --------------------------------------------- WDMECH001384 NADSUSEA NADSUSEA WDMECH001384 WDMECH001384 JOSEPH.RESSLER MAC Address = 00-08-74-C9-9E-D0 H:\nbtstat -a 10.22.23.24 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy text between two different delimeters | New Users to Excel | |||
copy text from 1 workbook to another by macro | Excel Worksheet Functions | |||
Macro to delete a text box and copy in new one | Excel Discussion (Misc queries) | |||
Macro to copy text from ColumnA to B | Excel Programming | |||
Macro to copy daily text file | Excel Programming |