PDA

View Full Version : Excel Macro to find records exist in workbook



reVidia
09-03-2008, 11:41 AM
Hi, I am trying to write a macro to take in a range of cells and then display a text next to it to indicate if the value of each respectively cell exist anywhere in the workbook.

I roughly know the flow but as I am still new to vba i am facing issue to get the codes out.

1. 1st input box ask for the range start
2. 2nd input box ask for range end
3. loop from the first cell in the range, get cell value
4. declare ws as worksheet, for each ws in worksheet, find the value
5. if value exist in workbook, indicate found else put not found
6. loop till end.

Would be glad if someone can give me some pointers thanks.

mdmackillop
09-03-2008, 01:15 PM
Welcome to VBAX
Give this a try
Sub DoFind()
Dim txt, sh As Worksheet, c As Range
txt = InputBox("Text to find")
For Each sh In Sheets
Set c = sh.Cells.Find(txt, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox "Text found at" & vbCr & sh.Name & c.Address
Exit Sub
End If
Next
End Sub

reVidia
09-04-2008, 10:38 AM
Not exactly what i wanna implement but i guess its good enough for me to pick up from, thanks alot mate !

mdmackillop
09-04-2008, 10:59 AM
If you need a defined rangr then

Sub DoFind()
Dim txt, sh As Worksheet, c As Range, Addr As String
Addr = InputBox("Enter address in A1:C5 format")
txt = InputBox("Text to find")
For Each sh In Sheets
Set c = sh.Range(Addr).Find(txt, lookat:=xlWhole)
If Not c Is Nothing Then
MsgBox "Text found at" & vbCr & sh.Name & c.Address
Exit Sub
End If
Next
End Sub