Excel: new list from cells in a range 1

[yingxyang]

Hello, say i have a range of cells on sheet1: A1:E10.
i'd like to copy the value of any cell where the "LEN" of the value in that cell is greater than 6 and less than 9, and create a single colum list of those values on column A of sheet2. the direction (seach by rows or columns) and sort order of the output column is not important.
Can anyone show the vba code to do this?
Thanks!

 

[OzzieTheOwl]

Try This

Sub Example()
    Dim LoopName As Long
    Dim PasteRow As Long
    PasteRow = 1
    Dim Answer As Long
    For LoopName = 1 To 10
        If Sheets("Sheet1").Range("A" & LoopName) < 9 And Sheets("Sheet1").Range("A" & LoopName) > 6 Then
            Answer = Sheets("Sheet1").Range("A" & LoopName)
            Sheets("Sheet2").Range("A" & PasteRow) = Answer
            PasteRow = PasteRow + 1
        End If
    Next LoopName
End Sub

 

[MacroAlan]

Basically it is 2 nested For...Next statements

Sub ExtractLen()
    Dim ColNum As Long, RowN As Long, Z As Long, daVal As String
    Z = 1
        For ColNum = 1 To 5
            For RowN = 1 To 10
        daVal = Cells(ColNum, RowN) 'get contents
            If Len(daVal) > 6 Then
                If Len(daVal) < 9 Then
                    Sheets("Sheet2").Cells(Z, 1) = daVal
                    'if both conditions met, write the value
                Z = Z + 1
                End If
            End If
        Next RowN
    Next ColNum
End Sub

Alan

 

[yingxyang]

Thanks Guys,
After some tweaking I wound up with this...

Sub makelist()
Dim Z As Long
Z = 1
For Each c In Sheets("Sheet1").Range("A1:E100")
If Len(c) < 9 And Len(c) > 6 Then
Sheets("Sheet2").Cells(Z, 1) = c
Z = Z + 1
End If
Next
End Sub

Thanks for your help!