Can’t remember the code to use

[ipbr21054]

I have a specific cell which is J9

I have a spreadsheet of user information.

Now I cant remember the name of the code to use but when a name is entered into cell J9 this code would then look at the spreadsheet in column D for the same name & select its cell.

Thanks.

 

[MARK858]

Assuming that you have headers in column D maybe...

Sub findit()
    On Error Resume Next
    Columns("D:D").Find(Range("J9").Value, , , xlWhole, , xlNext).Select
    On Error GoTo 0
End Sub

If you are running the macro, If you need the code to run automatically when J9 is typed in then post back.

 

[ipbr21054]

I was thinking of running it automatically but what happens when cell J9 is empty ?

Also if John was entered into J9 would the code go straight to the first John in my column as if I hadn’t finished and started to enter his surname would the code just keep searching the column until I stopped typing or an exact match was found assuming more than 1 John is in the column.

 

[MARK858]

It would run when you pressed the enter button and would only find an exact match in column D (i.e. the whole name would have to match exactly including the surname).

 

[ipbr21054]

Ok
I asked as you mentioned to post back if it was to be run automatically, without hitting the enter button.
It then gives me two options to try and see which I prefer.

Many thanks.

 

[MARK858]

You need to press the enter button or click another cell, code below to go in the worksheet module not a regular module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("J9")) Is Nothing Then
        If Target.Value <> "" And Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            On Error Resume Next
            Columns("D:D").Find(Target.Value, , , xlWhole, , xlNext).Select
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[ipbr21054]

Afternoon,

I have rearranged a few items so the cell ref above has altered etc but here goes.

Having typed a name in cell E3 & either clicking a cell or hitting enter i see a message Method "Find" of object "Range" failed
Where it show A:A below i tried A7:A just in case as cell A6 is quite busy but no joy

When i click on End the name is then selected & i cant then select any cell as if pc frozen ?

Code supplied below.
Thank you

Private Sub Worksheet_Change(ByVal Target As Range)Dim r   As Range
    
    On Error GoTo errHandle
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
If Not Intersect(Target, Range("E3")) Is Nothing Then
        If Target.Value <> "" And Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            On Error Resume Next
            Columns("A:A").Find(Target.Value, , , xlWhole, , xlNext).Select
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If
        If Target.Address = "$A$6" Then
            With Sheets("INFO").Range("CG2")
                If Len(.Offset(1).Value) Then
                    Set r = .End(xlDown).Offset(1)
                    With .End(xlDown).Offset(1)
                        .Value = UCase$(ActiveSheet.Cells(6, 1).Value)
                        .Interior.ColorIndex = 6
                        .HorizontalAlignment = xlCenter
                        .VerticalAlignment = xlBottom
                        .VerticalAlignment = xlCenter
                        .Borders.LineStyle = xlContinuous
                        .RowHeight = 19.5
                        .Font.Bold = True
                        With ActiveWorkbook.Worksheets("INFO").Sort
                             .SetRange Range("CG2:CG500")
                             .Header = xlYes
                             .MatchCase = False
                             .Orientation = xlTopToBottom
                             .SortMethod = xlPinYin
                             .Apply
                        End With
                    End With
                End If
             End With
        End If
        
    With Target
        If .Column <> 13 And .Count = 1 And Not .HasFormula Then




            .Value = UCase$(.Value)
        End If
    End With
        
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
Exit Sub


errHandle:
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox Err.Description, vbCritical, "Error number: " & Err.Number
    
End Sub

 

[MARK858]

First of all after moving some code around tell us what happens when you run the code below including what the error message is and what line is highlighted.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range

    If Not Intersect(Target, Range("E3")) Is Nothing Then
        If Target.Value <> "" And Target.Cells.Count = 1 Then
            Application.EnableEvents = False
            On Error Resume Next
            Columns("A:A").Find(Target.Value, , , xlWhole, , xlNext).Select
            On Error GoTo 0
            Application.EnableEvents = True
        End If
    End If

    On Error GoTo errHandle

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If Target.Address = "$A$6" Then
        With Sheets("INFO").Range("CG2")
            If Len(.Offset(1).Value) Then
                Set r = .End(xlDown).Offset(1)
                With .End(xlDown).Offset(1)
                    .Value = UCase$(ActiveSheet.Cells(6, 1).Value)
                    .Interior.ColorIndex = 6
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlBottom
                    .VerticalAlignment = xlCenter
                    .Borders.LineStyle = xlContinuous
                    .RowHeight = 19.5
                    .Font.Bold = True
                    With ActiveWorkbook.Worksheets("INFO").Sort
                        .SetRange Range("CG2:CG500")
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
                End With
            End If
        End With
    End If

    With Target
        If .Column <> 13 And .Count = 1 And Not .HasFormula Then




            .Value = UCase$(.Value)
        End If
    End With

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    Exit Sub


errHandle:

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    MsgBox Err.Description, vbCritical, "Error number: " & Err.Number

End Sub

 

[ipbr21054]

Much better but need a few things still.

I typed in 3 different names then enter,selected that name in the column perfect.

If i type TOM JONESS & it should of been TOM JONES then i should see a msgbox saying not found it i think because at present i see a Run Time Error Message 91, Object variable or with block variable not set.
If i click on debug this part is shown in yellow Columns("A:A").Find(Target.Value, , , xlWhole, , xlNext).Select

Closing down the page & then entering a name that worked just now will not select it in column A & no error is shown.
For it to work correctly again with that same name i need to save,close then open again.

There is something else which is a minor thing but lets get the above sorted first please.

 

[MARK858]

If i type TOM JONESS & it should of been TOM JONES then i should see a msgbox saying not found it

That is because you didn't ask for it in the question in this thread, it is from the code in your other thread that you have merged the code with, so first step replace

On Error Resume Next

with

On Error GoTo errHandle

or create a second error handler with your desired message and refer to that.