Range.Find help

[jyokom]

I am receiving a runtime error 91 for the following code. It occurs at the newrow = Nme.Range("L4:L70").Find(newnme).Row line.

Private Sub Cmd_Add_Click()
Dim nextrow As Integer
Dim newnme, nxtnme As String
Dim newrow, wkrow As Range
Dim rw, n As Integer
Set Wkly = Sheets("Weekly")
Set Nme = Sheets("Names")
Set Dty = Sheets("AddDuty")

    nextrow = Nme.Range("A3").End(xlDown).Row + 1
        With Nme
            .Range("A" & nextrow).Value = UCase(Txt_LastName)
            .Range("B" & nextrow).Value = UCase(Txt_FirstName)
            .Range("C" & nextrow).Value = Cbo_Rank
            .Range("D" & nextrow).Value = UCase(Cbo_CrewPos)
            If Chk_Attch = True Then
                .Range("E" & nextrow).Value = "X"
            End If
            newnme = .Range("L" & nextrow).Value
        End With

n = 4
For rw = 5 To 72 Step 2
    Wkly.Range("A" & rw).Value = Nme.Range("A" & n) & " " & Left(Nme.Range("B" & n), 1)
    Wkly.Range("B" & rw).Value = Nme.Range("D" & n)
    n = n + 1
Next rw
    If Nme.Range("E" & n) = "X" Then
            Nme.Shapes("Txt_Attached").Copy
    End If

    Nme.Sort.SortFields.Clear
    Nme.Sort.SortFields.Add Key:=Range("A4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Nme.Sort
        .SetRange Range("A4:E70")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Nme.Cells.Font.Superscript = False

newrow = Nme.Range("L4:L70").Find(newnme).Row

    nxtnme = Nme.Range("L" & newrow + 1).Value
        With Wkly
            rw = .Range("A5:A70").Find(what:=nxtnme).Row
            wkrow = .Range("A5:A70").Find(what:=newnme).Row
                .Rows(rw & ":" & rw + 1).Cut
                .Rows(wkrow & ":" & wkrow + 1).Insert Shift:=xlDown
        End With

End Sub

 

[MUKESHY12390]

Is Newnme Available? in those range.

newrow = Nme.Range("L4:L70").Find(newnme).Row

 

[jyokom]

Yes. In debug mode, newrow = Empty and newnme = the correct info. Does the sort have anything to do with it?

 

[MUKESHY12390]

Does the sort have anything to do with it? ---- doesn't affect searching method.
nothing wrong with syntax " newrow = Nme.Range("L4:L70").Find(newnme).Row "... I would advice look closely.

Use F8 check all variable involved in this like ..... newRow, Nme and Newnme check their value and availablity while running,


--------------------------------------------------

below thing is not related to your issue I would declare variable in this away its easy to understand that's all.
when you don't declare data type it becomes variant.

Option Explicit


Sub checkVariableType_2()


'''''''You
Dim nextrow As Integer
Dim newnme, nxtnme As String
Dim newrow, wkrow As Range
Dim rw, n As Integer






MsgBox VarType(nextrow)
MsgBox VarType(newnme)
MsgBox VarType(nxtnme)
MsgBox VarType(newrow)
MsgBox VarType(wkrow)
MsgBox VarType(rw)
MsgBox VarType(n)




'Number VBA.VbVarType
'8192 (&H2000) vbArray
'0 vbEmpty
'1 vbNull
'2 vbInteger
'3 vbLong
'4 vbSingle
'5 vbDouble
'6 vbCurrency
'7 vbDate
'8 vbString
'9 vbOject
'10 vbError
'11 vbBoolean
'12 vbVariant
'13 vbDataObject
'14 vbDecimal
'17 (&H11) vbByte
'36 (&H24) vbUserDefinedType


'''''''You






End Sub




Sub checkVariableType()


'''''''this is better
Dim nextrow As Integer
Dim newnme As String, nxtnme As String
Dim newrow As Long, wkrow As Range
Dim rw As Long, n As Integer






MsgBox VarType(nextrow)
MsgBox VarType(newnme)
MsgBox VarType(nxtnme)
MsgBox VarType(newrow)
MsgBox VarType(wkrow)
MsgBox VarType(rw)
MsgBox VarType(n)




'Number VBA.VbVarType
'8192 (&H2000) vbArray
'0 vbEmpty
'1 vbNull
'2 vbInteger
'3 vbLong
'4 vbSingle
'5 vbDouble
'6 vbCurrency
'7 vbDate
'8 vbString
'9 vbOject
'10 vbError
'11 vbBoolean
'12 vbVariant
'13 vbDataObject
'14 vbDecimal
'17 (&H11) vbByte
'36 (&H24) vbUserDefinedType


'''''''You






End Sub

 

[jyokom]

I finally figured it out. There was a formula in the cell that it was trying to lookup. I used VBA to add the same info as text. I could have also used lookin:=

I appreciate the info on checking variable type. that will be useful in other projects.

 

[MUKESHY12390]

that's great , you found issue by your own.

keep it up!!