Can’t remember the code to use

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
Assuming that you have headers in column D maybe...

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
You need to press the enter button or click another cell, code below to go in the worksheet module not a regular module.

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Code:
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
 
Last edited:

MARK858

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
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.

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
2,614
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

Well-known Member
Joined
Nov 12, 2010
Messages
11,344
Office Version
365, 2010
Platform
Windows, Mobile
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
Code:
On Error Resume Next
with
Code:
On Error GoTo errHandle
or create a second error handler with your desired message and refer to that.
 
Last edited:

Forum statistics

Threads
1,078,366
Messages
5,339,770
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top