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

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

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





.

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





.