Page 1 of 6 123 ... LastLast
Results 1 to 10 of 53

Thread: Canít remember the code to use

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Canít remember the code to use

    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.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,091
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Canít remember the code to use

    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.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Canít remember the code to use

    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.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,091
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Canít remember the code to use

    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).
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Canít remember the code to use

    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.

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,091
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Canít remember the code to use

    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
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Canít remember the code to use

    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 by ipbr21054; Sep 22nd, 2018 at 12:08 PM.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,091
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Canít remember the code to use

    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
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    Board Regular
    Join Date
    Nov 2010
    Posts
    2,478
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Canít remember the code to use

    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.
    I have learning difficulties so please be patient if i'm slow on the uptake,Thanks Very Much...

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,091
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Canít remember the code to use

    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 by MARK858; Sep 22nd, 2018 at 12:46 PM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •