VBA can't find string

Question

I'm asking VBA to find a string, then an ending string, and copy all the rows inbetween to paste into another sheet. However when I run the code it can't find the string. I've tested the code in a separate file using the same two strings as a start and ending point and it works just fine.

After looking online I see that the cell format could be causing this, but I don't see how to change it or even if that is the cause in this case. Any help is appreciated

Dim findrow As Long, findrow2 As Long
On Error GoTo errhandler

findrow = Range("A:A").Find("0667 John Smith", Range("A1")).Row
findrow2 = Range("A:A").Find("TTl Hrs For Employee", Range("A" & findrow)).Row

Range("A" & findrow & ":A" & findrow2).Select
Selection.Copy
Sheets("Sheet2").Select
Range("C12").Select
ActiveSheet.Paste

errhandler:
MsgBox "No Cells containing specified text found"

Picture is example of what I'm trying to copy
If I type the exact same thing in a new document the code finds it no problem. But in the original I get an code 91 error at the first "findrow" line.

**(1)** You are accessing the data of the active sheet - is this what you want? **(2)** No need to use `Select` to copy a range, read https://stackoverflow.com/q/10714251/7599798 **(3)** The `On Error Goto` eats up all kind of errors, check what specific error you have **(4)** Your error handler is executed even if everything worked, you have to issue an `Exit Sub` (or `Exit Function`) after the `Paste` — FunThomas, Nov 15 '18 at 16:34
Look at adding some more arguments to the find aswell. Look at the help for find. — Nathan_Sav, Nov 15 '18 at 16:39
Consider checking the `LookIn`, `LookAt` and `MatchCase` settings, or explicitly setting them in the search. Also, comment out the `On Error GoTo errhandler` line temporarily in case the Error Message is suitably descriptive. Beyond that - example data would help us debug. — Chronocidal, Nov 15 '18 at 16:45
@FunThomas, Yes I want the data from the active sheet. I removed my error checker. I'm getting code 91, but I have all my objects set or at least I think I do. I'm still new to VBA. The debugger says my problem is in the first "findrow" line but I don't see how. — Battle Goose, Nov 15 '18 at 16:51
Most likely the text is not found - in that case `Find` returns `Nothing`, you are accessing the `Row` of it and that's not possible, VBA throws the error 91 — FunThomas, Nov 15 '18 at 17:00
@Chronocidal I've added a sample photo of what I'm trying to copy — Battle Goose, Nov 15 '18 at 17:01
Are you aware that you are searching only in the first column? — FunThomas, Nov 15 '18 at 17:02
@FunThomas Yes because that's where the two strings I'm looking for are. — Battle Goose, Nov 15 '18 at 17:07
Try to issue a Find with Excel for exactly the string. I am almost sure that it cannot be found - maybe you have extra spaces or NewLines in the cell? — FunThomas, Nov 15 '18 at 17:10
I agree with @FunThomas - try doing the search manually with Ctrl+F first to see if the text is actually there, and if not then search row-by-row and work out what the text *actually* says — Chronocidal, Nov 15 '18 at 17:15

score 0 · Answer 1 · answered Nov 15 '18 at 16:39

Assuming the values are present, this worked just fine for me:

findrow = Range("A:A").Find("0667 John Smith", Range("A1")).Row
findrow2 = Range("A:A").Find("TTl Hrs For Employee", Range("A" & findrow)).Row
Range("A" & findrow & ":A" & findrow2).Copy Worksheets("Sheet2").Range("C12")

as long as the sheet to search was active. You may want to specify it in the code:

findrow = Worksheets("Sheet1").Range("A:A").Find("0667 John Smith", Worksheets("Sheet1").Range("A1")).Row
findrow2 = Worksheets("Sheet1").Range("A:A").Find("TTl Hrs For Employee", Worksheets("Sheet1").Range("A" & findrow)).Row
Worksheets("Sheet1").Range("A" & findrow & ":A" & findrow2).Copy Worksheets("Sheet2").Range("C12")

score 0 · Answer 2 · answered Nov 15 '18 at 17:15

Update: Found out what was wrong with it. For some reason the Find function can't find either string in the original sheet. If I copy the whole sheet into a new sheet and then have my code search for the strings it finds them and copies them just like it should. I don't know what's causing it to do that and its not efficient but hey, it works. Thanks you guys for helping me trouble shoot, special thanks to @FunThomas

score 0 · Answer 3 · answered Nov 15 '18 at 18:17

use fully qualified (up to worksheet object at least) range references and always specify Find() method LookAt and LookIn parameters not to implicitly assume last method usage (even from Excel UI) ones:

Dim firstCell As Range, lastCell As Range

With Worksheets("MySheetName") ' reference sheet where to serach for text (change "MySheetName" to your actual sheet name)
    Set firstCell = .Range("A:A").Find("0667 John Smith", Range("A1")) ' try and find first cell
    If Not firstCell Is Nothing Then ' if first cell found
        Set lastCell = .Range("A:A").Find(what:="TTl Hrs For Employee", lookat:=xlWhole, LookIn:=xlValues, after:=firstCell) ' try find last cell
        If Not lastCell Is Nothing Then ' if last cell found
            .Range(firstCell, lastCell).Copy Sheets("Sheet2").Range("C12")
        Else
            MsgBox "No Cells containing specified 'TTl Hrs For Employee'"
        End If
    Else
        MsgBox "No Cells containing specified '0667 John Smith'"
    End If
End With

VBA can't find string

3 Answers3