Aranell
05-05-2014, 07:15 AM
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
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