Lookup on multiple sheets in file

[Steph]

Hello. I have an excel workbook with 90 sheets in it. Each sheet is
identical in format and size (like a template). Somewhere between B50 and
B200 of each sheet there may or may not be in the cell the word "Payroll".
I need to remove the contents of columns C through Z on all lines that have
the word "Payroll" in column B, and do this on every sheet. Is there a
simple way to code this? Thanks!

 

[Dave Peterson]

You could loop through the range looking for Payroll. And when you find it,
clear those cells.

VBA has a nice example for this kind of thing.

Then you could loop through all the worksheets in your workbook.

Kind of like:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim FirstAddress As String
Dim LookForStr As String

LookForStr = "payroll"
For Each wks In ActiveWorkbook.Worksheets
With wks
FirstAddress = ""
With .Range("b:b")
Set FoundCell = .Cells.Find(what:=LookForStr, _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Resize(1, 24).ClearContents
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
End With
Next wks
End Sub


I looked for payroll anywhere in the cell in column B (xlpart).

 

[Steph]

Perfect! Thanks Dave!!

You could loop through the range looking for Payroll. And when you find
it,
clear those cells.

VBA has a nice example for this kind of thing.

Then you could loop through all the worksheets in your workbook.

Kind of like:

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim wks As Worksheet
Dim FirstAddress As String
Dim LookForStr As String

LookForStr = "payroll"
For Each wks In ActiveWorkbook.Worksheets
With wks
FirstAddress = ""
With .Range("b:b")
Set FoundCell = .Cells.Find(what:=LookForStr, _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Offset(0, 1).Resize(1, 24).ClearContents
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress
End If
End With
End With
Next wks
End Sub


I looked for payroll anywhere in the cell in column B (xlpart).