Find

animas

Active Member
Joined
Sep 28, 2009
Messages
396
Code:
            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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

larrydunn

Board Regular
Joined
Jun 1, 2003
Messages
130
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

Board Regular
Joined
Jun 1, 2003
Messages
130
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

Active Member
Joined
Sep 28, 2009
Messages
396
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

Board Regular
Joined
Jun 1, 2003
Messages
130
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top