FindNext help in Excel macro 1

[Solutus]

I have an excel macro to find the word "student" and then insert a row below. But I need it to loop and basically keep finding all throughout the worksheet. I think it uses FindNext but I am a novice with VB. Could someone help me with this code?

Here is what I have:

Cells.Find(What:="student", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Worksheets("Sheet1&quot.Activate
ActiveCell.Offset(rowOffset:=1).Activate
Selection.Insert Shift:=xlDown


Thanks!

 

[Zathras]

There are a few unanswered questions here.

Are you really looking for "student" anywhere in the worksheet? Any and all columns? If you find "student" in more than one column do you want a blank row for each instance?

Do you really want to look in formulas rather than the values?

Using find next can be tricky because it is easy to find the same thing again. In particular, when you find the last you will still be able to find next. I.e., infinite loop.

Inserting rows changes the location of data so that in general it is better to work from the bottom up rather than from the top down.

If you only need to search one column, here is a routine that should do what you want. If "student" can appear in any column, post back and I can modify the code appropriately.
[blue]

Option Explicit

Sub test()
  InsertRows "B", "student"
End Sub

Sub InsertRows(Column As String, SearchText As String)
Dim rng As Range
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nCol As Integer
Dim nRow As Long
Dim sSearchFor As String

[green]

' Find last row

[/color]

  With ActiveSheet
    With Intersect(.UsedRange, .Columns(Column))
      nLastRow = .Offset(.Rows.Count).Row - 1
      nFirstRow = .Row
      nCol = .Column
    End With
  End With

[green]

' Loop thru column inserting rows where search text is found

[/color]

  sSearchFor = LCase(SearchText)
  For nRow = nLastRow To nFirstRow Step -1
    If InStr(LCase(Cells(nRow, nCol)), sSearchFor) > 0 Then
      Cells(nRow + 1, nCol).EntireRow.Insert
    End If
  Next nRow
End Sub

[/color]

 

[Solutus]

Sorry I didn't give all the info. I have about 45,000 rows all in 1 column (a roster). I already have a macro to transpose the data but I need to have a blank row between each group first. The last row of each group is the person's status, ie student.

Thanks for the tip on FindNext, I don't want that infinite loop!!!!

I tried your code and get an "Object Variable or With Block area not set" error, it looks like problems with

nLastRow = .Offset(.Rows.Count).Row - 1

Thanks so much for your help, I feel I am a little over my head on this one.

--Nerdgrrl

 

[Zathras]

Ah. Sorry about that. I had no idea you had so much data. I was trying a new technique to find the last row of the "used range" but I see now it is useless. Replace the center section of the code with the following and it should be ok.
[green]

' Find last row

[blue]

  With ActiveSheet
    With Intersect(.UsedRange, .Columns(Column))
      nLastRow = .Rows.Count + .Row - 1
      nFirstRow = .Row
      nCol = .Column
    End With
  End With

[/color][/color]
Please forgive me for using you as a test site!