Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting all matches | Excel Worksheet Functions | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
matches | Excel Discussion (Misc queries) | |||
matches | Excel Discussion (Misc queries) | |||
Looking for matches | Excel Programming |