Autocopy the first cell with more than 30 characters to a different sheet?

[Excelnoob100]

Hi. I am using Excel 2010.
I have a workbook with a web query.
Is it possible to autocopy the first cell that has more than 30 characters to a different sheet?
It would help me a lot, because I then could see if there were any changes from the Website.

Example:

"Sheet1"
Range F1:F238.

F1:F20 String < 30 characters. Don't autocopy
F21: => 30 characters. Then copy to "sheet2" A1

Thanks in advance.

 

[hippiehacker]

Sub Excelnoob100()
Dim i As Long
i = 1
With Sheets(1)
For Each cell In .Range("F1:F238")
 If Len(cell) > 29 Then
  cell.Copy Sheets(2).Range("A" & i)
  i = i + 1
 End If
Next cell
End With
End Sub

 

[Excelnoob100]

Thanks.
But I don't want all values containing > 30 characters. Only the first! "For Each cell In .Range("F1:F238") For first cell In. Range?

I found this forumula: =INDEX(List,MIN(IF(LEN(List)>30,(ROW(List)*(LEN(List)>30))-MIN(ROW(List))+1,99999))). Can you make a code out of this?

 

[hippiehacker]

Sub Excelnoob100()
Dim i As Long

With Sheets(1)
For Each cell In .Range("F1:F238")
 If Len(cell) > 29 Then
  cell.Copy Sheets(2).Range("A1")
   Exit Sub
 End If
Next cell
End With
End Sub

 

[Rick Rothstein]

Give this macro a try (change the StartRow assignment to match your actual setup)...

Sub MoveFirstRowOver30Characters()
  Dim LastRow As Long, Addr As String
  Const StartRow As Long = 2
  With Worksheets("Sheet1")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    Addr = "Sheet1!A" & StartRow & ":A" & LastRow
    On Error GoTo NoCellWithMoreThan30Characters
    .Rows(Evaluate("MIN(IF(LEN(" & Addr & ")>30,ROW(" & Addr & ")))")).Copy Worksheets("Sheet2").Range("A1")
  End With
NoCellWithMoreThan30Characters:
End Sub