Macro to read the value entered to a cell in one sheet and search for a match in another sheet and return a value

[KTSARA]

Hello experts,
I have two columns (Machine code and Date) in a sheet (Sheet7)to which data is entered through a userform. There is another column called E-mail in this sheet. Machine codes and respective E-mail addresses are in another sheet (Sheet1-Machine Registry) as well. What I want to do is, when a particular machine code is entered to Sheet 7 through the userform, search for that machine code in Sheet1 and return the respective E-mail address to E-mail column in Sheet7. I tried to use a VLOOKUP function, thought of using a macro as it is more efficient than using a VLOOKUP to 30000 rows.

This is the VLOOKUP I used,

=VLOOKUP(A2,'[Sheet1]MACHINE REGISTRY'!$C$2:$M$20000,11,0)

I am new at Excel VBA and despite my efforts I cannot seem to find a similar example online to use as a solution to my issue. So, any help from you experts is highly appreciated.

Thanks a lot in advance

 

[Snakehips]

@KTSARA
Various ways you might do this.

You obviously have have userform code that populates Sheet7 with Machine code and date.
Maybe add similar to below to that code.

'Edit to suit
Dim NextRow As Long
Dim MyForm As String

NextRow = ActiveCell.Row
'Assuming your current data entry row is known and is assigned to a variable eg >> NextRow
'then....
Set EMailCell = Sheets("Sheet7").Range("E" & NextRow)  'NextRow, column E of 'Sheet7'

'formula to find email addrrss
MyForm = "=IFERROR(VLOOKUP(A" & NextRow & "," & "'MACHINE REGISTRY'" & "!$C$2:$M$20000,11,0),""***No Email Found***"")"
'To populate EMailCell
With EMailCell
.Formula = MyForm
.Value = .Value
End With

Hope that helps

 

[KTSARA]

@KTSARA
Various ways you might do this.

You obviously have have userform code that populates Sheet7 with Machine code and date.
Maybe add similar to below to that code.

'Edit to suit
Dim NextRow As Long
Dim MyForm As String

NextRow = ActiveCell.Row
'Assuming your current data entry row is known and is assigned to a variable eg >> NextRow
'then....
Set EMailCell = Sheets("Sheet7").Range("E" & NextRow)  'NextRow, column E of 'Sheet7'

'formula to find email addrrss
MyForm = "=IFERROR(VLOOKUP(A" & NextRow & "," & "'MACHINE REGISTRY'" & "!$C$2:$M$20000,11,0),""***No Email Found***"")"
'To populate EMailCell
With EMailCell
.Formula = MyForm
.Value = .Value
End With

Hope that helps

Hello,
Thank you very much for taking your time to answer my question and sorry for the late reply. I inserted your code in my Service Details Userform code under the click event of Submit command button. Then I get a run time error saying that Subscript out of range and highlight the below line of code.

Set EMailCell = Sheets("Sheet7").Range("C" & nextAlert)

I am sending the userform data of machine code and date to two sheets at once. Is it affecting the final result?
This is the code I am using.

Sheet7.Activate

nextAlert = WorksheetFunction.CountA(Range("A:A")) + 1

wsServiceReminders.Cells(nextAlert, 1).Value = MachineCodeComboBox.Value
wsServiceReminders.Cells(nextAlert, 2).Value = CDate(NextServiceDateTextBox.Value)
Set EMailCell = Sheets("Sheet71").Range("C" & nextAlert)
MyForm = "=IFERROR(VLOOKUP(A" & nextAlert & "," & "'MACHINE REGISTRY'" & "!$C$2:$M$20000,11,0),""***No Email Found***"")"

With EMailCell
.Formula = MyForm
.Value = .Value
End With

End With

Can you please help me to find out the error here.

Thanks a lot in advance

 

[KTSARA]

Hello experts,
For the above question, I modified a code I found in the internet and placed it in sheet 7 code window, but nothing happens when I run the code. Can some out there help me to find why?
Here is the code I used.

Private Sub findEmail()

Dim FindString As String
    Dim Rng As Range

    For Each cell In Range("A2:A20000")
        FindString = cell.Value
       
        If Trim(FindString) <> "" Then
            
            With Sheets("Sheet1").Range("C2:C20000")
                Set Rng = .Find(What:=FindString, _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                 
                    email = ActiveCell.Offset(0, 11).Value
                  
                    cell.Offset(0, 2).Value = email
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If

    Next
End Sub

Any help from you is highly appreciated.
Thanks a lot in advance