Jagwarman
asked on
Find cell with specific word
can someone out there provide me with a bit of code that will find a specific word in colomn A in Excel. [lets say word is Reporting] so it needs tocover both upper and lowercase.
Thank you
Thank you
ASKER
Thanks R_Edwards but I need it to be VBA as it is part of a macro.
In excel's "home" tab locate the "Find and Select" all the way to the right of the window then run a search for 'Records'
Make sure the entire column A is selected
Hope this helps.
Make sure the entire column A is selected
Hope this helps.
to find and select you can use:
With Selection.Find
.Forward = True
.Wrap = wdFindStop
.Text = "Hello"
.Execute
End With
Range("A:A").Find("reporti ng", , xlValues, xlWhole, , , False).Select
ASKER
Thanks RobZimmerman but I am looking for some VBA code
No selection required and highlights the word(s if more than one).
Sub FindWord()
Dim lngLastRow As Long
Dim lngIndex As Long
lngLastRow = Range("A65536").End(xlUp).Row
For lngIndex = 1 To lngLastRow
If Cells(lngIndex, 1) = "test" Then ' change to suit
Cells(lngIndex, 1).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = vbYellow
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next
End Sub
ASKER
Just to clarify, when the VBA finds the cell I need the macro to 'Select' the cell i.e. I need to end up on that cell
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sub FindWord()
Dim lngLastRow As Long
Dim lngIndex As Long
lngLastRow = Range("A65536").End(xlUp). Row
For lngIndex = 1 To lngLastRow
If Cells(lngIndex, 1) = "test" Then ' change to suit
Cells(lngIndex, 1).Select
Exit Sub
End If
Next
End Sub
Dim lngLastRow As Long
Dim lngIndex As Long
lngLastRow = Range("A65536").End(xlUp).
For lngIndex = 1 To lngLastRow
If Cells(lngIndex, 1) = "test" Then ' change to suit
Cells(lngIndex, 1).Select
Exit Sub
End If
Next
End Sub
You could use this one line:
or if you want to select ALL cells this may do it...
ActiveSheet.Range("A:A").Find(What:="Reporting", After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
or if you want to select ALL cells this may do it...
Sub Macro1()
Dim rng As Range, cell As Range, hid As Range
Set rng = ActiveSheet.Range("A1:A5000")
For Each cell In rng
If UCase((cell.Value)) Like "*REPORTING*" Then
If hid Is Nothing Then
Set hid = cell
Else: Set hid = Union(hid, cell)
End If
End If
Next cell
On Error Resume Next
hid.Select
End Sub
http://office.microsoft.com/en-us/excel-help/check-if-a-cell-contains-text-HP003056106.aspx