Can’t remember the code to use

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Changing the items mentioned above still does the same thing & same line of code shown in yellow
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I don't see how you are getting the error on that line when the On error resume next line was there. Please upload a copy of your workbook to www.box.com, mark it for sharing and post the link it provides in the thread so I can see if the other code you added or your setup is affecting it.
 
Last edited:
Upvote 0
That appears to be fine, I will have a proper look at it once I have had some tea.
 
Upvote 0
Upvote 0
All that code does is open Honda sheet and go to cell A13 I believe.

I deleted the other sheets and just uploaded these two.
 
Upvote 0
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 GoTo errHandle2
            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
errHandle2:
    MsgBox "Name not found"
    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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top