Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup on multiple sheets in file
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup on multiple sheets in file
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup on multiple sheets in file
Perfect! Thanks Dave!!
"Dave Peterson" wrote in message ... 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup across multiple columns within multiple sheets | Excel Discussion (Misc queries) | |||
LOOKUP across Multiple Sheets | Excel Worksheet Functions | |||
V-Lookup from multiple sheets | Excel Discussion (Misc queries) | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) |