I'm new to VB and I am trying to write a code to find, copy, and paste

[jlroeder]

I work on a Nortel SL100 and the report I print out puts all my information in column A. I am trying to write a code to copy the LEN,to the next worksheet each in its own column. I am able to find, copy and paste the first LEN, but it won't progress to the next cell down in sheet 2.

 

[SkipVought]

hi,

This is a job for one the many Excel Spreadsheet Features.....

Data > Text to columns...

where you can parse the data in column A into multiple columns. No VBA required. However, you can turn on your macro recorder and record the process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlroeder]

I wish it was that easy. Each line of text is in its own cell.

cell a1>-----------------------------------------------------------cell a2>--------------------
cell a3>LEN: HOST 00 0 00 09
cell a4>TYPE: SINGLE PARTY LINE
cell a5>DN 1234567891 LCC M5312 SET SIG N/A LNATTIDX N/A
cell a6>CUSTGRP: MCAS SUBGRP: 0 NCOS: 31 RING: Y
cell a7>CARDCODE: 6X21AC GND: N PADGRP: PONS BNV: NL MNO: Y
cell a8>PM NODE NUMBER : 23
cell a9>PM TERMINAL NUMBER : 33
cell a10>DNGRPS OPTIONS:
cell a11>NETNAME: NONE
cell a12>NAME: NONE
cell a13>OPTIONS: $

all this repeats many times in column A.

 

[SkipVought]

So the data titles are
[tt]
LEN, TYPE, CUSTGRP, CARDCODE, PM NODE NUMBER , PM TERMINAL NUMBER , DNGRPS OPTIONS, NETNAME, NAME, OPTIONS
[/tt]
and a5 has no data title? so where does it go?

and why do some of your data titles have trailing spaces like [highlight]PM NODE NUMBER [/highlight]???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlroeder]

A5 has multiple information. It has DN(phone number), LCC(type of phone)and other information I do not wish to copy and past.
DN 1234567891 LCC M5312 SET

 

[SkipVought]

Thnks for that ADDITIONAL info.

However, you did not answer the questions!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlroeder]

Sorry, That's just how it prints the report.

 

[MakeItSo]

Also:
How do you wish to deal with lines that obviously contain multiple fields?
cell a7>CARDCODE: 6X21AC GND: N PADGRP: PONS BNV: NL MNO: Y

Shall CARDCODE, GND, PADGRP, BNV and MNO become separate column headers too?

Cheers,
MiS

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jlroeder]

That informantion is not required.

 

[SkipVought]

Then please explain in detail, exactly what info is required, and exactly how you want to map this data from sheet1 to sheet2.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MakeItSo]

Well then:
WHICH of the information posted is required and in what form.
Please give concise information on the required output.


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

LOL! Near simultaneous posting...

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jlroeder]

All I was really trying to do was to find the LEN, DN and Options and past them into its own cell.
For example
column A would have LEN, coulmn B would have the DN, and coulmn C would have the Options.

The code I had started to write was able to find all the LENs, but it wasn't pasting correctly. It wouldn't progress to the next cell down in sheet 2.

 

[PHV]

The code I had started to write
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Please post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlroeder]

Sub findlen()


Do Until ActiveCell.Value = Empty
Sheets("nslens").Cells.Find(What:="len:", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Copy
ActiveCell.Offset(1, 0).Activate


Sheets("Sheet2").Ranges(a1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Loop

 

[SkipVought]

are there one or more empty rows between each group down column A?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jlroeder]

There are no blank cells.

 

[MakeItSo]

You say

the report I print out puts all my information in column A.

Is the output directly into Excel or is it some text format that you only open in Excel?

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jlroeder]

The output is captured as a text file and then I open it in Excel.