Macro to copy only certain text

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

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
 
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
 
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



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
Click to expand...


.
Click to expand...
 
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 said:
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



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
Click to expand...


.
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to interpret nbtstat results????? 0
networking problem 4
net view shows "no entries in list" via name but works via IP 1
Troubleshooting Netbios 7
XP Networking HElp 2
Macro to copy text from ColumnA to B 4
Cannot ping desktop from laptop or router 6
Connecting Win 98 Clients to Win 2000 server 2

Back
Top