Find a Text

fari1

fari1

Active Member
Joined
May 29, 2011
Messages
362
Hi, i'm seeking for a code which may find the word balance in any row of the sheet, whether in the start or in the mid or at the end of text string in a cell, i mean whereever it looks for the word BALANCE, it brings it in, also i further want to make the validation in the code, that if the word is found in a cell where LEN is more than 50 words, then dont give that cell as an output.
hope i made my point my clear
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
When you say 'brings it in', I assume you're refering to the output, but I think you might have to be a little more specific.
 
Upvote 0
This prints the results in the immediate window
Code: 
Sub findBalance()
    Dim f As Range, fa As String
    Set f = Cells.Find(What:="balance", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not f Is Nothing Then
        fa = f.Address
        Do
            If Len(f.Value) < 50 Then
                Debug.Print "found in " & f.Address
            End If
            Set f = Cells.FindNext(f)
        Loop Until f.Address = fa
    End If
End Sub
 
Upvote 0
Alternatively, this will dump the results into a predefined sheet, column 1

Code: 
Sub findBalance()
    Dim f As Range, fa As String, i As Long
    Dim src As Worksheet, dst As Worksheet
    Set src = Sheets("sheet1") 'sheet to be searched, change as required
    Set dst = Sheets("sheet2") 'sheet for output, change as required
    i = 2
    With dst
        .Columns(1).ClearContents
        .Range("A1") = "output"
        Set f = src.Cells.Find(What:="balance", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not f Is Nothing Then
            fa = f.Address
            Do
                If Len(f.Value) < 50 Then
                    .Cells(i, "A") = "found in " & f.Address
                    i = i + 1
                End If
                Set f = src.Cells.FindNext(f)
            Loop Until f.Address = fa
        End If
    End With
End Sub
 
Upvote 0
hi, the code is working fine, but why its not giving the cell addresses in which cell values are starting with the word balance e.g balance sheet, its only providing the cell address of the cells which contain the word balanace within it, e.g notes to balance sheet.
Also this code is just the providing the cell address for the values from column A, while i want it to loop through the whole worksheet, every column
 
Upvote 0
Code: 
Sub findBalance()
    Dim f As Range, fa As String, i As Long
    Dim src As Worksheet, dst As Worksheet
    Set src = Sheets("sheet2") 'sheet to be searched, change as required
    Set dst = Sheets("sheet3") 'sheet for output, change as required
    i = 2
    With dst
        Set f = src.Cells.Find(What:="CONSOLIDATED", After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not f Is Nothing Then
            fa = f.Value
            Do
                If Len(f.Value) < 50 Then
                    .Cells(i, "A") = fa
                    i = i + 1
                End If
                Set f = src.Cells.FindNext(f)
            Loop Until fa = f.Value
        End If
    End With
End Sub

okay then i've amended the code to get cell values and not the address, want the above changes in this code
 
Upvote 0
Sorry, but the code does look in the whole sheet, it just displays the results in column A of another sheet. Perhaps you need to be a little clearer on how you need the results to be displayed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Comparing characters in two strings and looking for a difference
Replies
2
Views
84
dgparryuk
D
S
  • Question Question
Copy Formula result as value into find
Replies
4
Views
147
myall_blues
myall_blues
J
  • Question Question
vba to find specific text in a column and then when found copy all the rows above it
Replies
1
Views
186
mumps
mumps
zookeepertx
  • Solved
Trying (unsuccessfully) to pull a date from the middle of a string
2 3
Replies
23
Views
529
zookeepertx
zookeepertx
A
  • Question Question
Error 5854 - String Parameter Too Long
Replies
2
Views
614
Artik
Artik

Forum statistics

Threads
1,224,584
Messages
6,179,687
Members
452,938
Latest member
babeneker

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top