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]

If your column of data is in Sheet1 and your want the output in Sheet2...

Sub test()
    Dim r As Range, rng As Range, a, ia As Integer
    Dim lRow As Long, iCol As Integer
    
    With Sheet1
        Set rng = .[A1].CurrentRegion
        
        If rng.Rows.Count = 1 Then
            Set rng = rng.End(xlDown).CurrentRegion
        End If
        
        lRow = Sheet2.[A1].CurrentRegion.Rows.Count + 1
        
        Do
            
            
            For Each r In rng
            'split on COLON
                a = Split(r.Value, ":")
            'if there is no COLON, then split on SPACE
                If UBound(a) = 0 Then a = Split(r.Value, " ")
                
                With Sheet2
                'element ZERO is the FIELD HEADING
                'element ONE is the FIELD VALUE
                    Select Case a(0)
                        Case "LEN"
                            .Cells(lRow, 1).Value = "'" & a(1)
                        Case "DN"
                            .Cells(lRow, 2).Value = "'" & a(1)
                        Case "DNGRPS OPTIONS"
                            .Cells(lRow, 3).Value = "'" & a(1)
                            lRow = lRow + 1
                    End Select
                    
                End With
            Next
            Set rng = rng.End(xlDown).End(xlDown).CurrentRegion
            
            If rng.Rows.Count = 1 Then Exit Do
        Loop
    End With
End Sub

Skip,

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

 

[jlroeder]

Thanks