strFindWhat Issue

[isidearmi]

Hello,

The code below used to work, but it was pulling from data that was set up as such:

Operator Name = "J Doe"

The quotes were since removed and the raw data now looks like this:

Operator Name = J Doe

How do I adjust this code below to pull correctly again?

Thank you!



blnSkip = False


strFindWhat = "Operator Name"

openPos = InStr(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, """")

If blnSkip = False Then
closePos = InStrRev(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, """")
strOperatorName = Mid(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, openPos + 1, closePos - openPos - 1)
End If

 

[Norie]

If you want to find a string starting with 'Operator Name' in column A and then extract the name after the '=' you could try something like this.

Dim Res As Variant

    Res = Application.Match("Operator Name*", .Range("A:A"), 0)

    If Not IsError(Res) Then
        If InStr(.Range("A" & Res).Value, "=") Then
            strOperatorName = Split(.Range("A" & Res).Value, "=")(1)
        End If
    End If

 

[MartyS]

Hello,

The code below used to work, but it was pulling from data that was set up as such:

Operator Name = "J Doe"

The quotes were since removed and the raw data now looks like this:

Operator Name = J Doe

How do I adjust this code below to pull correctly again?

Thank you!



blnSkip = False


strFindWhat = "Operator Name"

openPos = InStr(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, """")

If blnSkip = False Then
closePos = InStrRev(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, """")
strOperatorName = Mid(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, openPos + 1, closePos - openPos - 1)
End If

Leaving your code as similar as possible:

    blnSkip = False




    strFindWhat = "Operator Name"


    openpos = InStr(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, "=") + 1


    If blnSkip = False Then
        closepos = Len(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value)
        strOperatorName = Mid(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, openpos + 1, closepos - openpos)
    End If

I'm assuming this is part of a larger piece of code (as the .Range doesn't work unless it's contained within With ActiveSheet or With Sheets(i), etc and End With​)

 

[isidearmi]

Yes, it's a part of a longer piece of code. I came up with a similar solution to yours, and am now receiving a "Subscript out of range" error.

 

[MartyS]

Yes, it's a part of a longer piece of code. I came up with a similar solution to yours, and am now receiving a "Subscript out of range" error.

Without more information (i.e. what the actual input is, to this section of code) it's very hard to ascertain what's going on.

At a guess, I'd say there is an entry where it says "Operator Name" in the cell, but maybe doesn't have an equals sign in there... or is missing a name completely.

You could add the following in:


where it says:

strOperatorName = Mid(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, openpos + 1, closepos - openpos)

change it (temporarily) to:

On Error Resume Next
  strOperatorName = Mid(.Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value, openPos + 1, closePos - openPos)
  If Err.Number <> 0 Then MsgBox .Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Row & " - " & .Range("A:A").Find(What:=strFindWhat, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByRows).Value
On Error GoTo 0

and then run the code again.

Assuming that was the line where the error was occurring, you'll now get a msgbox telling you the ROW in the sheet and the CONTENTS of cell A in that row that it's falling over on (assuming that's the line of code that's the problem)

 

[Norie]

In the code I posted this checks to see if there is a '=' in the cell,

      If InStr(.Range("A" & Res).Value, "=") Then

and if there is it extracts the name after the '='.