Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)

[brianv]

I need to find a specific cell located in Column A:A on sheet 2 based on Sheet 1 D5, which is text based. I am not a code writer, but i find examples and work to modify them.

I need to select that Sheet2 cell in order to use it as a baseline to copy data from a "EditData" UserForm I have built. I have this built a 'NewEntry" Userform to add new data to a last row of a table based on using a "Find Last Row in Column A" macro.

So now i created a modified version of that "NewEntry" Userform to 'edit' existing data in a specific previous row of a table that corrosponds to the JobNumber listed in cell D5 on sheet1, so i need a starting point.

This was the original find last row code:

Private Sub CmdSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Active")

Sheets("Active").Select
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
 
'copy the data to the database
    ws.Cells(iRow, 1).Value = "Yes"

ws.Cells(iRow, 2).Value = Me.TextJobNumber.Value
ws.Cells(iRow, 3).Value = Me.TextProjectName.Value
ws.Cells(iRow, 4).Value = Me.TextAddress.Value
ws.Cells(iRow, 5).Value = Me.TextCSZ.Value
ws.Cells(iRow, 6).Value = Me.TextCustomer.Value
' Data continues for 20 cells...
    
    Range("B10").Select
        
    Sheets("Dashboard").Select
    Range("I6").Select

End Sub

So i tried to create a new macro to find a specific cell:

Sub FindTarget()

    Dim entry As String
    Dim mycol As Integer
    Dim myrow As Integer
    
    entry = Sheet1.Range("E7").Value
    mycol = Month(Now()) - 1
    myrow = Application.WorksheetFunction.Match(Range("e7"), Sheet1.Range("A1:A100"), 0)
    Sheet2.Range("a1").Select

End Sub

But im getting a runtime error.

Ultimately, this will reside in the UserForm code, I was just trying to get the code to 'find and select' the target cell first.

Any assistance is appreciated.

Thanks
BV

 

[brianv]

I got it working... found this on another post:

Macro to match value and select cell

Hi, Hopefully this will be a simple request.. I am in need of a macro that will take the value (which is text) in cell A3 of Sheet2 and search for the matching text in Column A of Sheet1. Sheet1 also has months on row 1 (in format "mmm-yy" and will always be the 1st of the month). When a...

www.mrexcel.com

Sub FindCell()

' Find the last row number in column A.
    Dim lastRow As Long
    With Sheets("Sheet2")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

' Find the value you are looking for
    lookupvalue = Worksheets("Sheet2").Range("C4").Value
 
' Select the range you are looking in
    lookuprange = Worksheets("Sheet2").Range("A1:A" & lastRow)
 
' Find the first matching value, and return the row number
' If there is no matching number, the macro will jump to "ErrorMessageBox"
    On Error GoTo ErrorMesageBox
    FirstMatchRowNumber = WorksheetFunction.Match(lookupvalue, lookuprange, 0)

' Go to the applicable row.
    Worksheets("Sheet2").Range("A" & FirstMatchRowNumber).Select
Exit Sub

' In case of an unknown number, the macro will show an message box
ErrorMesageBox:
    MsgBox "The number entered in B1 is not known in A1:A" & lastRow
    Exit Sub
End Sub