Lookup and add info

[Woosha]

Hello,

I have a list of 5000 patients in column A.

I have a form that

i want the user to be able to enter patients name found in column A then in textbox 2 and 3 populate column B and C of that patient and also be able to add information on that current patient that he searched for Columns D,E,F,G,H,I,J.

Any ideas?

https://ibb.co/bDVTmn

 

[mumps]

It would be easier to help if you could upload a copy of your file instead of a picture. This would make it easier to test a possible solution. De-sensitize the data if necessary.

 

[Woosha]

It would be easier to help if you could upload a copy of your file instead of a picture. This would make it easier to test a possible solution. De-sensitize the data if necessary.

The closer i got so far is this code but it's not perfect, for example when you enter patients name or a number ( which doesnt need to be a number to search but the code i found was using Integers), it doesn't populate the Hospital no(textbox2) and DOB(textbox3) but let's you update the rest text 4 to textbox 10.

Problem is if the value doesn't exist it will create one which doesn't have to do that and i think it's case sensitive which doesn't have to be again just to be able to find exact match or autocomplete the name while you type.

Hope that helps still working on it but not an expert :/

Dim id As String, i As Integer, j As Integer, flag As Boolean

Private Sub cmdClose_Click()


Unload Me


End Sub
Private Sub cmdClear_Click()


ClearForm


End Sub
Private Sub cmdUpdate_Click()


EditAdd


End Sub


Private Sub Reg1_Change()


GetData


End Sub


Private Sub UserForm_Initialize()


TextBox1.SetFocus


End Sub


Sub GetData()


If IsString(UserForm1.TextBox1.Value) Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value


    Do While Cells(i + 1, 1).Value <> ""


        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
            Next j
        End If


        i = i + 1


    Loop


    If flag = False Then
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If


Else
    ClearForm
End If


End Sub
Sub ClearForm()


For j = 1 To 3
    frmLookup.Controls("TextBox" & j).Value = ""
Next j


End Sub
Sub EditAdd()


Dim emptyRow As Long


If UserForm1.TextBox1.Value <> "" Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1


    Do While Cells(i + 1, 1).Value <> ""


        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 4 To 10
                Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
            Next j
        End If
12
        i = i + 1


    Loop


    If flag = False Then
        For j = 1 To 3
            Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
        Next j
    End If


End If


End Sub

 

[mumps]

Any possibility of uploading a de-sensitized copy of the file?

 

[Woosha]

Any possibility of uploading a de-sensitized copy of the file?

Here it is, Column A should be the patients names only i just use the numbers as well as it's the only that will populate values.

https://ufile.io/cxiqm

Can't see an option to upload a file using the sites options.

Login: geo
pass: 1234

 

[mumps]

Click here to download your file. I have made changes to some of your macros. Hopefully, I understood correctly what you want to do. When you enter a patient number, Hospital No and DOB will be populated. When you complete all the other text boxes and click the 'Update' button, columns E to K will be populated.

 

[Woosha]

A big thanks Mumps appreciate it and you got it right just 1 change if easy. Instead of searching a patient by number, is it possible to change the formula to search by name surname maybe in a autofill field if possible or not?

 

[mumps]

Click here for your file. Instead of the number, enter the patient full name.

 

[Woosha]

Amazing thanks Mumps that's all i wanted appreciate it

 

[mumps]

My pleasure.