1

Using For Each and the Find method, I can look for a cell in a range even if the range is multiple columns by multiple rows. But how can I look for a set of cells in a range?

For example, I have a reference book whose row entries fill rows 2-7 and are two columns wide. I need to find if they have an exact match to the row entries (which also begin in row 2 and are two columns wide) in my target book. If it finds a match, it pastes the row # from the reference file into column 5. Otherwise, it pastes in the new entry. Here's the code so far:

Dim NewFile as Workbook  'defined as the location of the reference book  
Dim NewSht as Worksheet  'defined as the sheet in NewFile  
Dim ThisSht as Worksheet  'defined as the sheet in the target book
Dim NewName as Range  
Dim LastEntry as Range  
Dim OldNameRange as Range  
Dim EntryMatch as Range  
For Each NewName In NewSht.Range(NewSht.Cells(2, 1), NewSht.Cells(Rows.Count, 2).End(xlUp)) 'loops thru each set of entry labels in new sheet  
  Set LastEntry = ThisSht.Cells(Rows.Count, 2).End(xlUp)
  Set OldNameRange = ThisSht.Range(ThisSht.Cells(2, 1), LastEntry)
  Set EntryMatch = OldNameRange.Find(NewName.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)  
  If Not EntryMatch Is Nothing Then  
    ThisSht.Cells(EntryMatch.Row, 5).Value = NewName.Row  
  Else  
    ThisSht.Cells(LastEntry.Row + 1, 1).Resize(1, 2).Value = NewSht.Range(NewSht.Cells(NewName.Row, 1), NewName.Cells(NewName.Row, 2)).Value  
    ThisSht.Cells(LastEntry.Row + 1, 3).Value = NewName.Row  
  End If  
Next

Right now, it's only comparing cell by cell. Can anyone help me get it to compare a set of cells (each row of entries) against each set of cells in the OldNameRange? Thanks!

Community
  • 1
  • 1
user2815713
  • 29
  • 5
  • You might want to check [THIS](http://stackoverflow.com/questions/19395633/how-to-compare-two-entire-rows-in-a-sheet/19396257#19396257) post by @TimWilliams – Siddharth Rout Oct 16 '13 at 16:12
  • @SiddharthRout, very interesting!! I'll see what I can do using `Join`. Thanks for the lead! – user2815713 Oct 16 '13 at 17:07
  • @SiddharthRout, I've been trying unsuccessfully to get `Join` to work. I've only been able to get it to show one column of values, though I tried join two columns per row. For now, I've chosen to loop through each cell, concatenate the values row by row (using a holding variable and the magical ampersand), then comparing the reference entries against that. It's a messy solution, but it does the job. If you're interested in what I tried using `Join`, let me know and I'll post as an update to my question. – user2815713 Oct 16 '13 at 19:39
  • you can introduce a boolean type variable which will always be true unless there is no match between cells. –  Oct 17 '13 at 07:10

0 Answers0