Searching for matches
 

I'm sure there is an easy way to do this with VBA. I am trying t
search two ranges for matches. I tried to do a nested 'For each cel
in selection' loop, but you can't nest 'For' Loops. Any Suggestions

--
Message posted from http://www.ExcelForum.com

Searching for matches
 

as usual, post your coding efforts for comments.

--
Don Guillett
SalesAid Software

"pkohler " wrote in message
...
I'm sure there is an easy way to do this with VBA. I am trying to
search two ranges for matches. I tried to do a nested 'For each cell
in selection' loop, but you can't nest 'For' Loops. Any Suggestions?


---
Message posted from http://www.ExcelForum.com/

Searching for matches
 

Here is the code I tried, but it doesn't work due to the nested 'For
Loops:

Sub searchformatches()
Application.ScreenUpdating = False

Dim inp, comp, out As Range
Dim str As String

Set inp = Application.InputBox(Prompt:="Select range you would like t
find matches for", Type:=8)
Set comp = Application.InputBox(Prompt:="Select range you would like t
comare to:", Type:=8)
Set out = Application.InputBox(Prompt:="Select range where you woul
like the results to go:", Type:=8)

inp.Select
For Each cell In Selection
str = cell.Value
comp.Select
For Each cell In Selection
If cell.Value = str Then
out.Select
For Each cell In Selection
If cell.Value = "" Then
cell.Value = str
Exit For
Next cell
Else
Next cell
Next cell



Application.ScreenUpdating = True
End Su

--
Message posted from http://www.ExcelForum.com

Searching for matches
 

try something like this, dont cut n paste directly to your real workboo
as im new at this and this may not act as you wish!!

Private Sub Workbook_SheetSelectionChange(ByVal sh As Object, ByVa
Target As Range)
Dim myrange As Range
Dim t1 As String
Dim I1 As Integer
Dim res As Variant


If sh.Name = "THE SHEET NAME YOUR LIST IS ON GOES HERE" Then Exi
Sub
Set myrange = sh.Range("THE RANGE TO WORK ON")
If Not Intersect(myrange, Target) Is Nothing Then
With Worksheets("SHEET THE LIST IS ON")
t1=inputbox ("looking/entering matches","Match additio
box",""
res = Application.Match(t1, .Range(.Range("A2"), _
.Range("A2").End(xlDown)), 0)
End With
If Not IsError(res) Then
ActiveCell = t1

Exit Sub
Worksheets("SHEET NAME YOUR LIST IS ON").Visible = False
End If

I1 = MsgBox("Please try again " & vbCrLf & _
"" Entry not recognised ")
If ActiveCell < "SHEET NAME " Then
End If
End If
End Sub

Probably isnt exactly what you want, but it may be useful to you!

Simon

--
Message posted from http://www.ExcelForum.com