We help IT Professionals succeed at work.

macro help - search  a column for text string

1,449 Views
Last Modified: 2008-04-24
In Excel 2003, I'm trying to insert a blank row above and below the row containing the word "Total".  I've got the following code which works fine (though there's probably a more elegant way to do it).   However, I would like to be able to search for the word "Total" in a column other than column A.

I've tried to specify column C by replacing

Set FocusRange = Intersect(ActiveSheet.[A:A], ActiveSheet.UsedRange)

with
Set FocusRange = Intersect(ActiveSheet.[C:C], ActiveSheet.UsedRange)

but when I do this and run the macro, no lines are inserted anywhere at all.  It appears not to do anything.  What changes should I make to look for the text "Total" in column C instead of column A?  Thanks!

Public Sub InsertRows()

   Dim FocusRange As Range
   Dim Row As Long
   
   Set FocusRange = Intersect(ActiveSheet.[A:A], ActiveSheet.UsedRange)
   If Not FocusRange Is Nothing Then
      For Row = FocusRange.Row + FocusRange.Rows.Count - 1 To FocusRange.Row Step -1
         If InStr(1, ActiveSheet.Cells(Row, 1), "Total", vbTextCompare) > 0 Then ActiveSheet.Cells(Row + 1, 1).EntireRow.Insert
     
      Next Row
   End If
   
   Set FocusRange = Intersect(ActiveSheet.[A:A], ActiveSheet.UsedRange)
   If Not FocusRange Is Nothing Then
      For Row = FocusRange.Row + FocusRange.Rows.Count - 1 To FocusRange.Row Step -1
         If InStr(1, ActiveSheet.Cells(Row, 1), "Total", vbTextCompare) > 0 Then ActiveSheet.Cells(Row, 1).EntireRow.Insert
     
      Next Row
   End If
End Sub
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
You can also replace all occurrences of ActiveSheet.Cells(Row, 1) with FocusRange.Cells(Row), so any future changes to the column need only be done when setting the focus range.

Wayne
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
You might try using the .Find method to locate your Total row. It is faster than looping through the cells.

Public Sub InsertRows()
Dim FocusRange As Range, celTotal As Range
Set FocusRange = Intersect(ActiveSheet.[C:C], ActiveSheet.UsedRange)
Set celTotal = FocusRange.Find("Total", LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not celTotal Is Nothing Then
    celTotal.Offset(1, 0).EntireRow.Insert
    celTotal.EntireRow.Insert
End If
End Sub


Brad
I think that the first reply (my reply) answers the question. The next 2 replies improves the final solution.

BR,
Curt

Author

Commented:
Sorry, folks, I was sent to work in a remote office, then went on hoilday and completely forgot to update my question here and accept a solution.  

Curt, your solution provided the info I needed and your response came quickly.  The points are yours.  Again, apologies for the delay.

Chris
Thanks, glad that could help you!

Curt
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.