podarum
05-11-2018, 07:55 AM
Hi,
I am stuck on something I can't get though.
I created 2 input fields in Excel (Last Name and First Name)..Once the user inputs a last name (example "Smith") then all the Smiths in the database will be displayed, however if the user inputs First (eg. "Mike") and Last names ""Smith" only that particular person will be displayed.
My problem is, I can get either get Mike Smith or only Smiths, but not if First Name is blank give me all Smiths, else if not blank then give me Mike Smith... below is my code: Thank you
Sub finddata3()
Dim firstname As String
Dim lastname As String
Dim broker As Integer
Dim finalrow As Long
Dim i As Integer 'row counter
Sheets("Form").Range("A34:J400").ClearContents
firstname = Sheets("Form").Range("D5").Value
lastname = Sheets("Form").Range("G5").Value
broker = Sheets("Form").Range("J5").Value
finalrow = Sheets("Broker_Data_Clean (FORM)").Range("A4000").End(xlUp).Row
For i = 2 To finalrow
If (Sheets("Broker_Data_Clean (FORM)").Cells(i, 2) = lastname And firstname = " ") Then
Range(Sheets("Broker_Data_Clean (FORM)").Cells(i, 1), Sheets("Broker_Data_Clean (FORM)").Cells(i, 12)).Copy
Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
ElseIf (Sheets("Broker_Data_Clean (FORM)").Cells(i, 1) = firstname And Sheets("Broker_Data_Clean (FORM)").Cells(i, 2) = lastname) Then
Range(Sheets("Broker_Data_Clean (FORM)").Cells(i, 1), Sheets("Broker_Data_Clean (FORM)").Cells(i, 12)).Copy
Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
End Sub
I am stuck on something I can't get though.
I created 2 input fields in Excel (Last Name and First Name)..Once the user inputs a last name (example "Smith") then all the Smiths in the database will be displayed, however if the user inputs First (eg. "Mike") and Last names ""Smith" only that particular person will be displayed.
My problem is, I can get either get Mike Smith or only Smiths, but not if First Name is blank give me all Smiths, else if not blank then give me Mike Smith... below is my code: Thank you
Sub finddata3()
Dim firstname As String
Dim lastname As String
Dim broker As Integer
Dim finalrow As Long
Dim i As Integer 'row counter
Sheets("Form").Range("A34:J400").ClearContents
firstname = Sheets("Form").Range("D5").Value
lastname = Sheets("Form").Range("G5").Value
broker = Sheets("Form").Range("J5").Value
finalrow = Sheets("Broker_Data_Clean (FORM)").Range("A4000").End(xlUp).Row
For i = 2 To finalrow
If (Sheets("Broker_Data_Clean (FORM)").Cells(i, 2) = lastname And firstname = " ") Then
Range(Sheets("Broker_Data_Clean (FORM)").Cells(i, 1), Sheets("Broker_Data_Clean (FORM)").Cells(i, 12)).Copy
Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
ElseIf (Sheets("Broker_Data_Clean (FORM)").Cells(i, 1) = firstname And Sheets("Broker_Data_Clean (FORM)").Cells(i, 2) = lastname) Then
Range(Sheets("Broker_Data_Clean (FORM)").Cells(i, 1), Sheets("Broker_Data_Clean (FORM)").Cells(i, 12)).Copy
Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats
End If
Next i
End Sub