• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Exel VBA to search matching TextBox value in column A not Working

A

arihan1511

New Member
Code: 
Private Sub CommandButton25_Click()
Worksheets("OPD").AutoFilterMode = False

Dim lastRow As Long
Dim FoundCell As Range
    Dim Search As String
   
Search = VisitDate.Value
Set FoundCell = Sheets("OPD").Columns(1).Find(Search, _
                                                    LookIn:=xlValues, _
                                                    LookAt:=xlWhole)
    If Not FoundCell Is Nothing Then
MsgBox "Duplicate Record Found in Database", vbInformation, "Database"
    Exit Sub
    Else
    'Dim lastrow As Long
   
    lastRow = Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select

UpdateData

MsgBox "Record Saved in Database", vbInformation, "Database"
        'MsgBox Search & Chr(10) & "Record Not Found", 48, "Not Found"
    End If
End Sub
Its Database of visiting customers.By clicking New Visit Todays date and time is displayed in TextBox named VisitDate which is unique id for that customer. After filling data when save visit button is hit I want to search date in column A matching VisitDate and if value is found Display message duplicate record and exit Sub Else Add Record
Above code is not working for searching matching date in column A.When I hit save button it keeps on adding records to last empty Rows creating duplicate records even when VisitDate value is unchanged

I have attached file for reference
 

Attachments

  • Prescription.xlsm
    118.7 KB · Views: 3
Thank for prompt reply
But Its Still not detecting duplicate value
what I want is After saving a new record ,if save button is clicked again immediately with other fields unchanged it should show duplicate record so that if button is pressed by mistake it should not save the record

Your code as well as my codes is working very well with column B and Column C
Only column A with Dates is having the issue
 
Last edited:
well you are right.Problem is with date format in visitdate and updated cell in first column. Both are fomatted to "dd/mm/yyyy hh:mm AM/PM" but cell value displays as mm/dd/yy format hence the mismatch
when date is in double digit ie 10 to 31 it is displayed as "dd/mm/yyyy hh:mm AM/PM"
But when date is in single digit ie from 1 to 9 it automatically displays as "mm/dd/yyyy hh:mm AM/PM" in spite of formatting otherwise hence code does not pick up duplicate value for single digit date due to mismatch

Can I get help to sort out this issue so that all dates wheather single or double digit are displayed in format "dd/mm/yyyy hh:mm AM/PM" in worksheet
This will resolve my problem
 
arihan1511 said:
Can I get help to sort out this issue so that all dates wheather single or double digit are displayed in format "dd/mm/yyyy hh:mm AM/PM" in worksheet
This will resolve my problem
Click to expand...

I would suggest this..

Code: 
Cells(a, 1) = CStr(Format(VisitDate.Text, "dd/mm/yyyy h:mm AM/PM"))

this will convert it to text.
 
Good Buddy!!

As u seen that there was issue with date format but due to wrong diagnosis by u we were start working on the wrong path.

To get the right solution on time it's always better to go in the deep of the issue.
 
You must log in or register to reply here.
Back
Top