Hello, just wanted to apologize if I don't seem clear as I am truely lost with VBA and this is actually my first post on these forums. Basically I have a workbook with two main spreadsheets, first as an input spreadsheet where a VBA form is used to input a record while the second serves as the actual database. Currently I have the form in working order with an error message that displays when all fields of the form aren't filled. I would like to add an error message for adding a duplicate entry but only when 3 of the fields match.
The form consists of an input for the following fields: Fund, Return, Date, Type
Fund- General Format (Or list, see rest of post)
Return- %
Date- mm/dd/yyyy format (Changing to list)
Type- List (Either "M" or "Q" can be selected)
I want an error message to occur when Fund, Date, and Type fields are already stored in the database even if the return field may be different when the form is used.
I made a forumla that brings a return only if the fund, date, and type fields match.
[/pre]
Thank you all in advance for your help!
*Updated with excel file
http://www.mediafire.com/view/?mn8lrz2o4onvwum
The form consists of an input for the following fields: Fund, Return, Date, Type
Fund- General Format (Or list, see rest of post)
Return- %
Date- mm/dd/yyyy format (Changing to list)
Type- List (Either "M" or "Q" can be selected)
I want an error message to occur when Fund, Date, and Type fields are already stored in the database even if the return field may be different when the form is used.
I made a forumla that brings a return only if the fund, date, and type fields match.
Code:
=INDEX(ReturnsData!D2:D9999,MATCH(1,(ReturnsData!C2:C9999=D21)*(ReturnsData!E2:E9999=Input!D23)*(ReturnsData!F2:F9999=Input!D25),0))
If there is a duplicate record with only the return field different, then there is no way to know which one it is pulling from.
Another spreadsheet will be added that serves as the list of possible funds available for selection for the input user form. I tried to use data validation, but it is cumbersome because it does not autosuggest possible funds when you begin to type and the list will be long. Is there a way to implement this as well? A way to begin to type a fund and giving you a suggestion of funds that match the "possible funds" list? Similar to autofill in a google search. While updating the possible funds dropdown if another fund is added to the "possible funds" list?
Here is the code as it is currently written:
[pre]Sub UpdateLogWorksheet()
Dim historyWks As Worksheet
Dim inputWks As Worksheet
Dim nextRow As Long
Dim oCol As Long
Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9, D11"
Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("ReturnsData")
With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With
With inputWks
Set myRng = .Range(myCopy)
If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With
With historyWks
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.GoTo .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End SubThank you all in advance for your help!
*Updated with excel file
http://www.mediafire.com/view/?mn8lrz2o4onvwum
