Hi Tom,

i have 3 sheets,
main data is in sheet1,(items and required quantity)
i have to find the items(random data) in sheet2 first,if i find the matching
check the quntity against the required qunatity of sheet1.
supply the quantity(based on whether it's in store1 or store2) and keep the
balance and the item number in sheet2.
if there is no matching then goto sheet2 and search for the item.and get the
matching qunatity.
i have around 150 to 200 rows,and keeps changing everyday.
pl read my post find the matching
the code is like this

Sub allocation()


With Sheets("polist")
sh1lastrow = .Cells(Rows.Count, "F").End(xlUp).Row
Set sh1range = .Range("F2:F" & sh1lastrow)
End With
With Sheets("slrs")
sh2lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set sh2range = .Range("A2:A" & sh2lastrow)
End With
With Sheets("FAB")
Sh3LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh3Range = .Range("A2:A" & Sh3LastRow)
End With

For Each sh1cell In sh1range
Set c = sh2range.Find( _
what:=sh1cell, LookIn:=xlValues)

If c Is Nothing Then

sh1cell.Interior.ColorIndex = 4
sh1cell.Offset(0, 1).Interior.ColorIndex = 4
Else

If sh1cell.Offset(0, 2) < c.Offset(0, 3) Then
sh1cell.Offset(0, 3).Value = sh1cell.Offset(0, 2)
c.Offset(0, 6).Value = sh1cell.Offset(0, 2)
c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]"
c.Offset(0, 5).Value = sh1cell.Offset(0, -3)
Else
If sh1cell.Offset(0, 2) c.Offset(0, 3) Then
sh1cell.Offset(0, 3).Value = c.Offset(0, 3)
c.Offset(0, 6).Value = sh1cell.Offset(0, 3)
Sheets("slrs").Range("G:G").NumberFormat = "0;[Red]0"
c.Offset(0, 4).FormulaR1C1 = "=RC[-1]-RC[2]"
c.Offset(0, 5).Value = sh1cell.Offset(0, -3)
Sheets("slrs").Range("F:F").NumberFormat = "0;[Red]0"
Range("F:F").ColumnWidth = 18

End If
End If
End If
Next sh1cell
End Sub

pl guide me
Ren

"Tom Ogilvy" wrote:

Sub bbb()
Dim rngA As Range, rngB As Range
Dim rng As Range, cell As Range
Dim res As Variant
With Worksheets("Sheet1")
Set rngA = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
Set rngB = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
End With
For Each cell In rngA
res = Application.Match("*" & cell.Value & "*", rngB, 0)
If Not IsError(res) Then
Set rng = rngB(res)
rng.Offset(0, 1).Value = cell
End If
Next

End Sub

worked for me. It assumes the lists start in A2 and B2. If in A1 and B1,
change

With Worksheets("Sheet1")
Set rngA = .Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
Set rngB = .Range(.Cells(1, 2), .Cells(1, 2).End(xlDown))
End With

--
Regards,
Tom Ogilvy

"Jim Jackson" wrote in message
...
In a spreadsheet I have one column with single item names and a second
column
with strings of data. I am trying to set up a routine that will search
the
column with strings for the names found in the first one. The result
needed
is to place the name in a third column adjacent to the one with the string
that contains the name. I have come up with this:

If InStr(Range("B2"), ActiveCell) Then Range("C2") = ActiveCell
If InStr(Range("B3"), ActiveCell) Then Range("C2") = ActiveCell
etc.

This works if I use "Offset" and type a separate line of code for each row
of data but this is rather inconvenient since there are over 1000 rows.

Is there a better way to accomplish this?

Thanks for any help anyone may offer.