Quote Originally Posted by VISHAL120
but the problem is as already mentioned its changing the Min_row_index instead of the max_row_index. which shall have been the contrary. that is why the data is not being updated. because as its going further by dividing to the last row it do not find anything.
This would happen if either the data is not sorted, or is not sorted in the right direction (ascending/descending) - you have not confirmed either of these.
Quote Originally Posted by VISHAL120
by the way vlookup function which also i have used before, how can i put the code that when i do not find the data itself it shall skip the row completely rather than stopping.
Use On Error Resume Next, and cancel it after the line with On Error Goto 0. Within the loop:[vba]xxx = "*^$%£)*&*^" ' any highly unlikely string.
On Error Resume Next
xxx = WorksheetFunction.VLookup(POS, Range("[Critical_Path_pm_6.xls]Stock!Stock_Data"), 2, False)
On Error GoTo 0
If xxx = "*^$%£)*&*^" Then
'it's not found, you may coose to do absolutely nothing, or do what you need, eg. state that it's not found in a cell.
Else
'it has been found
Range("[Yarn_Consumption_Data.xls]Yarn_consumption_Data!A1").Offset(Yarn_Cons_Row_Start - 1, gge_Column - 1) = xxx
End If
[/vba]