Matching values between two ranges

**hood8jmark** · 05-01-2008, 06:28 PM

Hi all,

I need help matching values between two ranges. I can't seem to figure out how to utilize VLOOKUP, FIND, or MATCH to suit my needs. With the following code I have set my search range to a list. I simply took a range in Sheet2 and converted it to a list which probably isn't necessary. At any rate, I now need to be able to search CloumnC in Sheet1 for matching values in this list and when found, populate the corresponding cell (in the same row of the found value) in ColumnE on Sheet1 with a value of "Late". The code below identifies the search range and converts it to a list. How do I implement VLOOKUP, MATCH or FIND or any other method for that matter to accomplish this.

Sub GetStatus()
Dim intlastrow As Integer
Dim i As Integer
Dim rng As Range
Dim cel As Range
Dim Stat As String
Dim newrng As String
Dim v As Variant
With Sheets(2).Activate
intlastrow = ActiveCell.CurrentRegion.Rows.Count
ActiveCell.Offset(intlastrow, 0).Select
Range("A2").Select

Set rng = Range("A2:A" & intlastrow)

'For i = 1 To intlastrow
For Each cel In rng.Cells
'Stat = Range("A2").Value
'MsgBox Stat
'MyForm.ListBox1.AddItem cel.Value
Stat = cel.Value
Sheets(1).Activate
newrng = Application.Match(Stat, Range("C2:C100"), 0) ' this fails
MsgBox newrng
'v = VLookup(Stat, newrng, 3, False)' I couldn't get this to compile
Next

'MyForm.Show
Sheets(1).Activate
End With
''vlookup
End Sub

**rylo** · 05-01-2008, 09:50 PM

Hi

Can you please review the forum rules about wrapping code. I've edited your post this time, but please remember to wrap your code in future.

For a solution, try this

Sub aaa()
  Dim WrkSH As Worksheet, rng As Range, findit As Range, ce As Range
  Set WrkSH = Sheets(1)
  
  With Sheets(2)
    Set rng = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
  End With
  For Each ce In rng
    Set findit = WrkSH.Range("C:C").Find(what:=ce.Value)
    If Not findit Is Nothing Then
      firstadd = findit.Address
      Do
        findit.Offset(0, 2).Value = "Late"
        Set findit = WrkSH.Range("C:C").Find(what:=ce.Value, after:=findit)
      Loop Until findit.Address = firstadd
    End If
  Next ce
End Sub

rylo

Matching values between two ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Matching values between two ranges

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions