Find function in VBA ?

[bastard_kestrel]

Hello,

I'm new to VBA and wondered if someone could help me out please, as I
don't really know my arse from my elbow ?

I've got an Excel spreadsheet with 2 sheets.

Sheet1 only has values in the first column (A1 to A50)
Sheet2 has values over a larger range (A1 to K500)

I need to be able to look at the value in Sheet1 A1 and search for it
in the range A1 to K500 on Sheet 2.

If the value appears anywhere on Sheet 2 I need to highlight the cell
(on sheet 2) somehow and also insert "found" next to the value (in
column B) on Sheet1.

If the value does not appear on Sheet 2 anywhere I need to check the
value in Sheet1 A2 against the range on Sheet2 . . . .and so on (Hope
this makes sense)

Is this possible ?

Any help or advice would really be appreciated

Thanks

 

[H.A. de Wilde]

Sub Macro1()
'
Dim lngA As Long
Dim lngB As Long
Dim varFind
Dim strFirstAddress As String

Worksheets("blad1").Activate
For lngA = 1 To Worksheets("blad1").UsedRange.Rows.Count
lngB = 2
With Worksheets("blad2").UsedRange
Set varFind = .Find(Worksheets("blad1").Cells(lngA, 1).Value,
LookIn:=xlValues)
If Not varFind Is Nothing Then
strFirstAddress = varFind.Address
..Range(varFind.Address).Interior.ColorIndex = 36
Cells(lngA, lngB) = strFirstAddress
lngB = lngB + 1
Set varFind = .FindNext(varFind)
Do While Not varFind Is Nothing And varFind.Address <>
strFirstAddress
..Range(varFind.Address).Interior.ColorIndex = 36
Cells(lngA, lngB) = varFind.Address
lngB = lngB + 1
Set varFind = .FindNext(varFind)
Loop
End If
End With
Next lngA
'
End Sub

 

[Rick Hansen]

Good Afternoon

here a bit of code you can try for you project, any question posted me
back...
enjoy,

Rick, (Fairbanks, AK, Land of the Midnight Sun )

Sub FindM()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim fndRng As Range, cell As Range
Dim firstAdd As String


Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
'Set rng1 = ws1.Range("A1:A50")
Set rng1 = ws1.Range("A1:A" & Range("A1").End(xlDown).Row)
Set rng2 = ws2.Range("A1:K500")

ws1.Activate
For Each cell In rng1
Set fndRng = rng2.Find(what:=cell.Value, _
LookIn:=xlFormulas, searchorder:=xlByRows)
If Not fndRng Is Nothing Then
firstAdd = fndRng.Address
Do
fndRng.Interior.Color = RGB(250, 250, 0)
cell.Offset(0, 1).Value = "Found"
If cell.Offset(0, 2).Value = "" Then
cell.Offset(0, 2) = fndRng.Address
Else
cell.Offset(0, 2) = cell.Offset(0, 2) & "," & fndRng.Address
End If
Set fndRng = rng2.FindNext(fndRng)
Loop While (fndRng.Address <> firstAdd)
Else
cell.Offset(0, 1).Value = "Not Found"
End If
Next cell

End Sub

 

[bastard_kestrel]

Brilliant - Thanks for your help folks !

Quick question Rick - If I wanted to expand or reduce the search range
on Sheet 1 (currently A1:A50) how would I alter it in your example ?

Cheers

 

[Rick Hansen]

Good Morning,
What I think your trying to ask me is how do determine the range of the
search list if it expands or contract on Sheet1. The way I wrote the code
automatically determines the size of the range for you, ie using this line
code:
Set rng1 = ws1.Range("A1:A" & Range("A1").End(xlDown).Row)
Excel starts at Cell A1 and Searchs Downward in Column A until finds the
first empty cell, thus determine the last row of the range. So if you to
the add or delete rows from the bottom of your range the code will always
determine lastrow of the range for you. Let me caution you, do not leave
empty cells in Column A, when excel finds the first empty cell it things it
at the end of the range. Thus giving a incorrect range size.
If you noticed I remarked out the line of : 'Set rng1 =
ws1.Range("A1:A50"), I let excel determine the range list size in line code
below it. The same line code I mentioned above. I hope this answered your
questiion. Good Luck on your project....

Rick, (Fbks, AK)

 

[bastard_kestrel]

Cheers for the explanation Rick

Thanks for all your help - you are a star