Excel VBA - issue with my VBA code

[NLU_VBA]

Dear all,

I have an Excel file with 2 worksheets - a form with 3 fields displayed in a single column (column C2, C3 and C4), and a database with the 3 same fields displayed in a table (A1, B1, C1).

List of fields: Incident iD, name of the reporter, Description of the event

Main behavior: when I fill in the form and press the button Ok, all the values displayed in a column are added as a row in the table > code OK
Then I have in the worksheet "DB" all the recorded incidents that can be analysed

Additional behavior: when I enter a value in the cell for the field "incident iD" , I want to see whether the incident value already exists in the database or not:
1/ If yes, when I press the "Enter" button, I should get the form filled in with all values recorded in the second worksheet and linked to the incident id value I entered;
2/ If not, when I press the button "Enter", I should get a message "New incident"


Here is the code I propose for the additional behavior:
"
Sub EnterIncident()
Dim wsForm As Worksheet
Dim wsDatabase As Worksheet
Dim Incident_id As Range
Dim lookupRange As Range
Dim FoundCell As Range

'Set worksheets
Set wsForm = ThisWorkbook.Sheets("1.Form - draft")
Set wsDatabase = ThisWorkbook.Sheets("2.HI database - automation")

'Define the range of Incident_id in wsForm
Set Incident_id = wsForm.Range("C5")

'Define the range to search in wsDatabase (column A)
Set lookupRange = wsDatabase.Range("A:A")

'Check if the incident iD already exists in the worksheet Database
Set Incident_id = wsDatabase.Range("A:A").Find(What:=Incident_id.Value, LookIn:=x1Values, lookat:=x1Whole)

' If Incident_id is found then populate the corresponding data in wsForm

' If Not FoundCell Is Nothing Then
wsForm.Range("C2").Value = FoundCell.Offset(0, 1).Value 'Incident_id
wsForm.Range("C3").Value = FoundCell.Offset(0, 2).Value 'Name of the reporter
wsForm.Range("C4").Value = FoundCell.Offset(0, 3).Value 'Description of the event

'If Incident_id is not found then show a message
MsgBox "Incident_id not found in the Database.", vbExclamation
End Sub"

When trying to execute this code I get an error debug message (see attached screenshot) with the aforementioned row in yellow.

Can you please help me?

Thanks a lot,
Franck

 

[dmt32]

Hi welcome to forum

You have couple of typos

Set Incident_id = wsDatabase.Range("A:A").Find(What:=Incident_id.Value, LookIn:=x1Values, lookat:=x1Whole)

If should be xl (lower case L) and you are also setting the incorrect range object variable to the Range.Find method.

try changing that line to this

Set FoundCell = lookupRange.Find(What:=Incident_id.Value, LookIn:=xlValues, lookat:=xlWhole)

'rest of code

Dave

 

[NLU_VBA]

Hi welcome to forum

You have couple of typos

Set Incident_id = wsDatabase.Range("A:A").Find(What:=Incident_id.Value, LookIn:=x1Values, lookat:=x1Whole)

If should be xl (lower case L) and you are also setting the incorrect range object variable to the Range.Find method.

try changing that line to this

Set FoundCell = lookupRange.Find(What:=Incident_id.Value, LookIn:=xlValues, lookat:=xlWhole)

'rest of code

Dave

Hello @dmt32

Thanks for your response!

Here are the results:
- when I enter the incident value and press "Enter" from the form, nothing happens;
- when I edit the code and press the button "Run Sub" I get for field 1 the field 2:
Value for Incident iD= "Name of the reporter"
Value for Name of reporter= "Description of the event"
Value for "Name of the reporter"= (Empty)

What I want is the list of values linked to the incident value I add (if already stored in the database) or only the message that it has not been found...

 

[dmt32]

Hi,
I take it from your post that the suggested update resolved the error issue?

As for returning the correct data - you need to be mindful that you are searching Column A in your database worksheet & to return the required data you need to specify the correct offset to the found range

Your posted code below Returns data in the found Cell from Columns B, C & D of that row. If this is not correct you need to amend the Offsets as required.

wsForm.Range("C2").Value = FoundCell.Offset(0, 1).Value 'Incident_id
wsForm.Range("C3").Value = FoundCell.Offset(0, 2).Value 'Name of the reporter
wsForm.Range("C4").Value = FoundCell.Offset(0, 3).Value 'Description of the event

Dave