-
Apr 16th, 2014, 06:57 PM
#1
Thread Starter
New Member
Code help to search sheets in Excel
Hello All,
I need some help writing some VB code in Excel to search all worksheets in a workbook (excluding 2 of them) for a specific string. When that string is found in any of the worksheets, I want to place a specific value in the cell 8 columns to the right. I can adapt the code as needed, I just need to find out a general set of code to do this. Please help.
Thanks!
-
Apr 17th, 2014, 02:47 PM
#2
Re: Code help to search sheets in Excel
Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt
-
Apr 17th, 2014, 03:28 PM
#3
Addicted Member
Re: Code help to search sheets in Excel
Code:
Sub SearchWord()
Dim strWord As String
strWord = "The value you are looking for"
Dim sh As Worksheet
Dim rng As Range
Dim c
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "ExcludeSheet1" And sh.Name <> "ExcludeSheet2" Then
Set rng = sh.UsedRange
Set c = rng.Find(strWord, LookIn:=xlValues)
If Not c Is Nothing Then
addr = c.Address
MsgBox addr
sh.Range(addr).Offset(0, 8).Value = "The specific value you wish place here "
Exit For
End If
End If
Next sh
End Sub
HTH
-
Apr 18th, 2014, 08:02 AM
#4
Re: Code help to search sheets in Excel
Does the find string appear only once in any given sheet?
-
Apr 18th, 2014, 08:14 AM
#5
Addicted Member
Re: Code help to search sheets in Excel
Originally Posted by vbfbryce
Does the find string appear only once in any given sheet?
I asked myself myself the same question.
I can not find my crystal ball
-
Apr 21st, 2014, 07:44 AM
#6
Thread Starter
New Member
Re: Code help to search sheets in Excel
Sorry, was away for the weekend. To answer the question, in this case, yes the string will only appear once on each sheet. However, this is code that I will likely use regularly so knowing how the code would look if the string will be found multiple times would be helpful as well. Will the code change if that is the case?
Thanks for the help!
-
Apr 21st, 2014, 08:09 AM
#7
Addicted Member
Re: Code help to search sheets in Excel
Code:
Sub SearchWord()
Dim strWord As String
strWord = "The value you are looking for"
Dim sh As Worksheet
Dim rng As Range
Dim c
For Each sh In ActiveWorkbook.Worksheets
If sh.Name <> "ExcludeSheet1" And sh.Name <> "ExcludeSheet2" Then
Set rng = sh.UsedRange
Set c = rng.Find(strWord, LookIn:=xlValues)
If Not c Is Nothing Then
addr = c.Address
sh.Range(addr).Offset(0, 8).Value = "The specific value you wish place here "
Do
addr = c.Address
Debug.Print sh.Name, addr
c.Value = strWord
Set c = rng.FindNext(c)
Loop While Not c Is Nothing And c.Address <> addr
End If
End If
Next sh
End Sub
HTH
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|