Help with a search and copy funktion

[Aranell]

Hi everyone

I need your help and support again.

I'm building a macro that search all values in Column A from Sheet2("SearchSheet") and compare it with all value beginning from Column A row12 in Sheet1("Result") and then I need to copy the entire rows of all matching cells to another sheet3 ("EndResult").

So far every thing is fine. and works if Sheet1 ("Result") is looking like this:



Column A
Column B


1
123456
blabla


2
124
blabla


3
123
blabla



here the code:


Private Sub CommandButton2_Click()

Dim S1 As Worksheet
Dim S2 As Worksheet

Dim i As Integer
Dim k As Integer
Dim j As Integer

Dim i2 As Long, iMatches As Long

Set S1 = Worksheets("Result")
Set S2 = Worksheets("SearchSheet")

Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "EndResult"

k = S1.UsedRange.Rows.Count
j = S2.UsedRange.Rows.Count

iMatches = 11

For i = 12 To k
If Application.WorksheetFunction.CountIf(S2.Range(S2.Cells(1, 1), S2.Cells(j, 1)), S1.Cells(i, 1).Value) > 0 Then
iMatches = (iMatches + 1)
Sheets("Result").Rows(S1.Cells(i, 1).Row).Copy Sheets("EndResult").Rows(iMatches)
End If
Next i

End Sub

with that code if I'm searching for example for 123 or/and 124
it will copy the entire Rows 2 and 3 (in the above example) to Sheet3 ("EndResult")


but what if the Shee1 is looking like this:



ColumnA
Column B


1
12345
blabala


2
Text
text1


3
124
blabla


4
Text 2
text2


5
Text 3
text3


6
123
blabla


7
Text 4
text4


8
5245
blabla


9
Text 5
text5



like the first example I need to search for 123 and 124. how can I copy all the rows 3 to 7 (so every row after the Row with the search criteria until the next "search" Point
Pls Note that I'm always searching for a 7 digit number and the related Rows below (Text) can be everything text, number etc...
Sheet 3 ("EndResult") needs to be like this:




Column A
Column B


1
124
blabla


2
Text 2
text2


3
Text 3
text3


4
123
blabla


5
Text 4
text4




Same thing if the Sheet1 is looking like that:





Column A
Column B


1
Text1
text1


2
123
blabla


3
1234
blaba


4
124
bla


5
Text2
text2


6
125
blabla


7
Text3
text3


8
5685
blabla



I need Sheet 3 looks like below if I'm searching for 123, 124 and 5685



Column A
Column B


1
Text1
text1


2
123
blabla


4
124
bla


7
Text3
text3


8
5685
blabla



I hope it's clear
thanks in adavance

[ValerieT]

I am not sure I understood everything but if I understand well, you want to copy all lines after a 7 digit number cells that match iMatch, until you meet another Imatch not matching...
why don't you create a "Flag" =1 when you meet the criteria, and = 0 when not (but a 7 digit number key), and copy raws as long as flag = 1
does it sound crazy?

[Aranell]

No Forget the iMatch
iMatch is only to difine where to copy in Sheet3

What I need is comparing all values in Column A in Sheet2 with the one in Column A Sheet1 and then coping all rows with matching values to sheet3.

The Problem is that sometimes I have rows after or before that are related to the one row with the specific value I'm searching for.



But I just got an Idea, I need to reformat Sheet1 so that in column A would be only the numbers I'm looking for.

I'll give a try and let you know

[snb]

Private Sub CommandButton2_Click()
sn=sheets("searchsheet").columns(1).specialcells(2)
sp=sheets("result").columns(1).specialcells(2)

for j=1 to ubound(sn)
if not iserror(application.match(sn(j,1),sp,0)) then sheets("endresult").cells(rows.count,1).end(xlup).offset(1).entirerow=sheets("searchsheet").rows(j).value
next
End Sub

Please post a workbook instead of html tables.