How to change the search area?

[Loukas Char]

Hello to everybody!

In an excel file with more than 40.000 rows (this might variate) and 50 columns, the code checks for specific strings in a specific area: going from the second row it checks from column C up to column G (or at least I think so), up to the last row (which is unknown because it is not stable). Then the rows which have at least one of the strings are copied/pasted, one below the other, to Sheet 2 up to column 50. Have a look at the code.

Sub findNcopy()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, fLoc As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", sh1.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng = sh1.Range("B2:G" & lr)
For i = 2 To lr
For j = 2 To 7
With sh1
If InStr(.Cells(i, j), "serv") > 0 Or InStr(.Cells(i, j), "financ") > 0 Or InStr(.Cells(i, j), "component") > 0 _
Or InStr(.Cells(i, j), "part") > 0 Or InStr(.Cells(i, j), "maint") > 0 Or InStr(.Cells(i, j), "install") > 0 _
Or InStr(.Cells(i, j), "repair") > 0 Or InStr(.Cells(i, j), "refurb") > 0 Or InStr(.Cells(i, j), "overhaul") > 0 _
Or InStr(.Cells(i, j), "procur") > 0 Or InStr(.Cells(i, j), "purchas") > 0 Or InStr(.Cells(i, j), "support") > 0 _
Or InStr(.Cells(i, j), "provis") > 0 Or InStr(.Cells(i, j), "lease") > 0 Or InStr(.Cells(i, j), "outsourc") > 0 _
Or InStr(.Cells(i, j), "licens") > 0 Or InStr(.Cells(i, j), "solut") > 0 Or InStr(.Cells(i, j), "solv") > 0 _
Or InStr(.Cells(i, j), "consult") > 0 Or InStr(.Cells(i, j), "advic") > 0 Or InStr(.Cells(i, j), "optimiz") > 0 _
Or InStr(.Cells(i, j), "optimis") > 0 Or InStr(.Cells(i, j), "assist") > 0 Or InStr(.Cells(i, j), "analys") > 0 _
Or InStr(.Cells(i, j), "turnkey") > 0 Or InStr(.Cells(i, j), "deliver") > 0 Or InStr(.Cells(i, j), "design") > 0 _
Or InStr(.Cells(i, j), "develop") > 0 Or InStr(.Cells(i, j), "custom") > 0 Or InStr(.Cells(i, j), "personali") > 0 _
Or InStr(.Cells(i, j), "tailored") > 0 Or InStr(.Cells(i, j), "adjust") > 0 Or InStr(.Cells(i, j), "traini") > 0 _
Or InStr(.Cells(i, j), "courses") > 0 Or InStr(.Cells(i, j), "pre-sal") > 0 Or InStr(.Cells(i, j), "after-sal") > 0 _
Or InStr(.Cells(i, j), "pre sal") > 0 Or InStr(.Cells(i, j), "after sal") > 0 Then
sh1.Range("A" & i).Resize(1, 50).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
Exit For
End If
End With
Next
Next
End Sub

I have 2 issues that I cannot resolve:

1. I cannot change the search area in order to search from column C up to column E for each line, from line 2 up to the last (which as said before is unknown but around 40.000). I tried to change the search area and the iterations but it doesn’t work.
2. When I use all these strings it crashes. Do you think I should reduce them somehow? Is there anything else I could do? Any suggestions?

If there is someone who could help I would much appreciate it.

Cheers,
Loukas

 

[pgc01]

Hi Loukas

Suggestion: write the strings in some range. You can then use Find() instead of all those comparisons.

For ex., if you write the strings in Z1:Z30 in Sh1, instead of

If InStr(.Cells(i, j), "serv") > 0 Or InStr(.Cells(i, j), "financ") > 0 Or InStr(.Cells(i, j), "component") > 0 _
    Or InStr(.Cells(i, j), "part") > 0 Or InStr(.Cells(i, j), "maint") > 0 Or InStr(.Cells(i, j), "install") > 0 _
    Or InStr(.Cells(i, j), "repair") > 0 Or InStr(.Cells(i, j), "refurb") > 0 Or InStr(.Cells(i, j), "overhaul") > 0 _
    Or InStr(.Cells(i, j), "procur") > 0 Or InStr(.Cells(i, j), "purchas") > 0 Or InStr(.Cells(i, j), "support") > 0 _
    Or InStr(.Cells(i, j), "provis") > 0 Or InStr(.Cells(i, j), "lease") > 0 Or InStr(.Cells(i, j), "outsourc") > 0 _
    Or InStr(.Cells(i, j), "licens") > 0 Or InStr(.Cells(i, j), "solut") > 0 Or InStr(.Cells(i, j), "solv") > 0 _
    Or InStr(.Cells(i, j), "consult") > 0 Or InStr(.Cells(i, j), "advic") > 0 Or InStr(.Cells(i, j), "optimiz") > 0 _
    Or InStr(.Cells(i, j), "optimis") > 0 Or InStr(.Cells(i, j), "assist") > 0 Or InStr(.Cells(i, j), "analys") > 0 _
    Or InStr(.Cells(i, j), "turnkey") > 0 Or InStr(.Cells(i, j), "deliver") > 0 Or InStr(.Cells(i, j), "design") > 0 _
    Or InStr(.Cells(i, j), "develop") > 0 Or InStr(.Cells(i, j), "custom") > 0 Or InStr(.Cells(i, j), "personali") > 0 _
    Or InStr(.Cells(i, j), "tailored") > 0 Or InStr(.Cells(i, j), "adjust") > 0 Or InStr(.Cells(i, j), "traini") > 0 _
    Or InStr(.Cells(i, j), "courses") > 0 Or InStr(.Cells(i, j), "pre-sal") > 0 Or InStr(.Cells(i, j), "after-sal") > 0 _
    Or InStr(.Cells(i, j), "pre sal") > 0 Or InStr(.Cells(i, j), "after sal") > 0 Then
        sh1.Range("A" & i).Resize(1, 50).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End If

write:

    If Not (.Range("Z1:Z30").Find(.Cells(i, j), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing) Then
        sh1.Range("A" & i).Resize(1, 50).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
    End If

 

[Loukas Char]

Hi and thanks for your reply.
It seems that it doesnt work. I get an error 13 type mismatch

 

[pgc01]

Hi

Try this simple test where I used a syntax similar to the one I posted.

I wrote in Z1:Z5 some values. In A1 I will write the value to find.

I then test one value, that it finds, and another, that is does not find.

Please try.

Sub Test()
Dim sh1 As Worksheet
Dim i As Long, j As Long
Dim b As Boolean

Set sh1 = Worksheets("Sheet1")
i = 1
j = 1

With sh1
    
    .Cells(1, 1) = "D"
    b = Not (.Range("Z1:Z5").Find(.Cells(i, j), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing)
    MsgBox "Result of find of " & .Cells(i, j) & " : " & b

    .Cells(1, 1) = "G"
    b = Not (.Range("Z1:Z5").Find(.Cells(i, j), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing)
    MsgBox "Result of find of " & .Cells(i, j) & " : " & b

End With
End Sub

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >Y</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >Z</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >AA</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">A</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">B</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">C</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">D</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">E</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[Loukas Char]

Hi PGC

I couldnt answer since my laptop crashed at some point and only today I managed to recover the lost items/data

I tried to do us you instructed but it works only for 3 rows, namely the 2nd, 3rd and 4th whereas I want it for a lot of rows.
In cells CC1:CC10 I wrote 10 strings which the code should find and then wrote some of the strings randomly in some of the cells. When executing the code it goes only for the 3 lines I mentioned and no more.

Could you help?

Below I have the code changed as you said.


Sub pipis()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, fLoc As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells.Find("*", sh1.Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set rng = sh1.Range("B2:G" & lr)
For i = 2 To lr
For j = 3 To 5
With sh1
If Not (.Range("CC1:CC10").Find(.Cells(i, j), LookIn:=xlValues, LookAt:=xlWhole) Is Nothing) Then
sh1.Range("A" & i).Resize(1, 50).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End If
End With
Next
Next
End Sub