Macro Find Entered Value and Insert Row

[didi1983]

Hi,

I have a cell that is populated based on a user's input (Cell E9). I would like to create a macro that will then look for the last instance of the value E9 in a set of values (E39:E4000) and insert a row below that row. It would then also paste the value currently in H9 in J of that new row.


Example

In E9 the user selects Suzy and in H9 they type 50. They run the macro.

Macro looks and finds that Suzy is in E50 to E57 so it creates a new blank row (now row 58) and in J58 it will enter '50'

 

[didi1983]

I've found a work around but still need help with Code. I have a row now that flags the last of a duplicated value in E39:E4000 and then assigns it a 1 if it matches the users input.

So now all I need is to Code so that it adds a row below the value '1' in column B and then copies the value from H9 in the new row. I will keep looking for ways around this as my VBA coding is weak but would love any suggestions.... currently recording macros and trying to read the code .

 

[Joe4]

Going back to your original question in post #1, here is code that should do everything you want:

Sub MyMacro()

    Dim myFind
    Dim myRow As Long
    
'   Get value to find (what is in cell E9)
    If Len(Range("E9")) > 0 Then
        myFind = Range("E9")
    Else
        MsgBox "You have not entered a value in E9 to find", vbOKOnly, "ERROR! TRY AGAIN!"
        Exit Sub
    End If
    
'   Search cell E39:E4000 for value to find
    On Error GoTo err_find
    Range("E39:E4000").Select
    Selection.Find(What:=myFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
        False, SearchFormat:=False).Activate
    On Error GoTo 0
    myRow = ActiveCell.Row
    
'   Insert new row
    Rows(myRow + 1).Insert
    
'   Copy value from H9 to column J of this row
    Cells(myRow + 1, "J") = Cells(9, "H")

    Exit Sub
    
    
'   Code to run if cannot find value
err_find:
    If Err.Number = 91 Then
        MsgBox "Cannot find " & myFind & " in E39:E4000", vbOKOnly, "PROCESS ABORTED!"
    Else
        MsgBox Err.Number & ":" & Err.Description, vbOKOnly, "UNEXPECTED ERROR!"
    End If
    
End Sub

 

[didi1983]

Thank you for your help!

 

[Joe4]

You are welcome!