Searching ranges for string values

[EUROSTOXX]

Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am trying to make a script that does the following: Say I have 2 ranges of data A: D and E: H. <o></o>
1) Need to arrange data in matching order (where column A and E are the names, and B,C,D & F,G,H are price, quantity etc) <o></o>
The name values in A & E are close but not exact. I was thinking of making a constant column, say on column J, with an identifier name, using the InStr function. So if A contains the string in J (InStr> 1) then I would like to cut the cell values in A:D and then paste them in K. Same thing with E, if E contains the string in J, cut and paste E:H into N (the next 3 rows over)<o></o>
I started making the script but I am getting stuck with my ForEach overwriting data (see code below). Any ideas how I can break out of the ForEach once I find a match?

Thank you kindly. I'm new to the forum but it looks like a great resource


<o>

</o:p><o:p></o:p>
<o:p>[FONT=Calibri][SIZE=3]Sub Macro1()[/SIZE][/FONT]</o:p>
[SIZE=3][FONT=Calibri]Dim string_constant As Range, check1 As Range, check2 As Range<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]Dim x, y As Variant<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim a, b, c As Integer<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]' Set Ranges<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set check1 = Workbooks("Book1").Worksheets("Sheet1").Range("A2:A40")<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set check2= Workbooks("Book1").Worksheets("Sheet1").Range("E2:E40")<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Set string_constant = Workbooks("Book1").Worksheets("Sheet1").Range("J2:J40")<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
<o:p></o:p>
[SIZE=3][FONT=Calibri]For Each x In string_constant<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]  'If IsEmpty(x) Then Move to next cell<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              ‘ but I cant figure out how to code this<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]  For Each y In check1<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      a = InStr(y, x)<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]     If IsEmpty(x) Then<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      a = 0<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      End If<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      If a > 0 Then<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]            <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      x.Offset(0, 1) = y<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]    ‘  y.ClearContents<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]‘ Next y. I get an error if I put this here. But I only want the script to check for values once. Once it find a match, I want it to move onto the next cell. I don’t want it to keep searching, as there may be overlaps. For example if my string is “Active” in A4 I have the values Active #2230, and in A30 = Active #2241, the A30 is the only one that will show.        <o:p></o:p>[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]      End If<o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      <o:p></o:p>[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Next y<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]      Next x<o:p></o:p>[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri]End Sub<o:p></o:p>[/FONT][/SIZE]

 

[baitmaster]

welcome to the board

If you want to jump out of a loop you can use EXIT FOR

Alternatively, you can use a named location in your code, name it pretty much anything unique and add a colon, such as

For Each x In string_constant<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
  If IsEmpty(x) Then [COLOR=blue]goto jumpToNextX[/COLOR]
    ' your code
 
 
[COLOR=blue]jumpToNextX[/COLOR][COLOR=blue]:[/COLOR]
next x

 

[EUROSTOXX]

thanks for the tip!

i wasted time trying to use 'Continue' but that is for VBA.net not excel vba... I'll try the exit