Find

[animas]

            Column A      Column B          Column C     Column D     Column E

Row 1     Batch No.        78
Row 2                     some text-1A      89789879
Row 3
Row 4                     some text-1A      89789879
Row 5                   
Row 6     Batch No.        75
Row 7                     some text-2A      42343243
Row 8
 Row 9                     some text-1B       42343243
Row 10

this thing will happen for each batch no.
if "text-1A" is found in both rows (for batch 78 row2 n row4 ) then column D (row 2 n row 4) = 89789879(for batch 78).

how to do it using vba code? may be 1 loop inside another loop and using Find Method etc. not sure. anyone can show some light?

 

[larrydunn]

I'm assuming your spreadsheet is exactly as described. Under those conditions, this code works.

Sub BatchMatch()
'Fill column D with column C value when column B text matches
Dim sMatchText As String 'Column B
Dim sMatchCode As String 'Column C
Dim sngMaxRow As Single

ActiveCell.SpecialCells(xlLastCell).Select 'End of spreadsheet
sngMaxRow = ActiveCell.Row

Application.ScreenUpdating = False
ActiveSheet.Range("A1").Select 'Start at upper left

FindNextBatch:
If ActiveCell.Value = "Batch No." Then GoTo NextTest
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
If ActiveCell.Row > sngMaxRow Then GoTo EndOfSubroutine
GoTo FindNextBatch

NextTest:
'See if cells in column contain the same text
sMatchText = ActiveSheet.Range("B" & ActiveCell.Row + 1).Value
If sMatchText = ActiveSheet.Range("B" & ActiveCell.Row + 3).Value Then GoTo FillColumnD
GoTo LeaveThisBatch

FillColumnD:
sMatchCode = ActiveSheet.Range("C" & ActiveCell.Row + 1).Value
ActiveSheet.Range("D" & ActiveCell.Row + 1).Value = sMatchCode

LeaveThisBatch:
ActiveCell.Offset(1, 0).Range("A1").Select 'Move down
GoTo FindNextBatch

EndOfSubroutine:
Application.ScreenUpdating = False
ActiveSheet.Range("A1").Select 'Back to upper left
End Sub

 

[larrydunn]

Oops!

The next-to-last line of my code reads:
Application.ScreenUpdating = False

Should read:
Application.ScreenUpdating = True

It doesn't matter much in this case, just that the last cell selected
doesn't get selected.

 

[animas]

worked fine with the data i posted.
following situation may arise:

"some text-1A 3232

ewew text-1Asas af"

a partial match(text-1A) should also show the same result.

 

[larrydunn]

This problem is pretty easy to solve if the text is "canned" - that is, if you know what the matching data ought to be. Either from a list of data pertaining to each item, or from the position of the 'sometext' within the current cell.

If the only criterion is that there be some matching data, somewhere within the values, then it becomes a different problem. Naturally, it would be very easy to get false matches, since any two items are very likely to have a match, even of a single letter, somewhere.

Please explain, or give examples, of how much variation there is in the data, or of what kind of clues can be relied on to prove a match.