Userform data entry question

[Torrez74]

I have a user form to enter 20 items into a sheet. the first Item entered on the form is the date (ComboBox populated buy column A on the worksheet). once the data is entered I want the VBA to find the corresponding date and input the data in the same row. below is the code I have for my Finishbutton_Click, Below that is the code I am working on for the search. Any and all help is appreciated.

Code

Private Sub FinishedButton1_Click()


Dim MCSheet As Worksheet


Set MCSheet = ThisWorkbook.Sheets("Meat Count")


nr = MCSheet.Cells(Rows.Count, 2).End(xlUp).Row + 1


MCSheet.Cells(nr, 1) = Me.Aspen_NYTextBox
MCSheet.Cells(nr, 2) = Me.Aspin_RibTextBox
MCSheet.Cells(nr, 3) = Me.BF12TextBox
MCSheet.Cells(nr, 4) = Me.BF18TextBox
MCSheet.Cells(nr, 5) = Me.KCTextBox
MCSheet.Cells(nr, 6) = Me.RIBTextBox
MCSheet.Cells(nr, 7) = Me.PORKTextBox
MCSheet.Cells(nr, 8) = Me.F12TextBox
MCSheet.Cells(nr, 9) = Me.F6TextBox
MCSheet.Cells(nr, 10) = Me.F8TextBox
MCSheet.Cells(nr, 11) = Me.NYTextBox
MCSheet.Cells(nr, 12) = Me.CHEFNYTextBox
MCSheet.Cells(nr, 13) = Me.PORTTextBox
MCSheet.Cells(nr, 14) = Me.BIGPORTTextBox
MCSheet.Cells(nr, 15) = Me.LAMBTextBox
MCSheet.Cells(nr, 16) = Me.CHEFRIBTextBox
MCSheet.Cells(nr, 17) = Me.TOMTextBox
MCSheet.Cells(nr, 18) = Me.VEALTextBox
MCSheet.Cells(nr, 19) = Me.A5TextBox
Range("AC5").Select
Unload Me
End Sub


Search Code

Private Sub CBDate_Change()
FormDate = CDate(Me.CBDate)


Dim searchDate As Range
Dim foundCell As Range
Dim mysearch As String


mysearch = Me.FormDate.Value


With ThisWorkbook.Sheets("Meat Count")
Set searchRange = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))
End With


Set foundCell = searchDate.Find(what:=mysearch, Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not foundCell Is Nothing Then
If foundCell.Offset(0, 1).Value = Me.FormDate.Value Then
Else
MsgBox "Date does not exist."
End If


End If
End Sub

 

[AlphaFrog]

If the CBDate combobox is populated with dates from column A, then the index number of a selected date in the combobox corresponds to a row number e.g.; if the CBDate is populated from cells A2 to A100, then the first date in the combobox is index number zero and it corresponds to row 2. So if you know the index number of the selected date (which you do), then just add 2 to its index number to get the row number it came from.

.ListIndex returns the index number of the selected item in the combobox.

MyRow = Me.CBDate.ListIndex + 2

 

[Torrez74]

Thanks for your comment but it wasn't working, I couldn't get it to return values. my code was wrong. I have moved in a different direction, I'm closer but still having trouble. Below is my code, my combobox is filled by the dates in (A2:A8) which are simple (=F1) formulas to populate the date. When I debug and select any date but the first in the range I get an error, when I select the first date (6/26/2018) The data inputs in the row below (the 6/27/2018 row). Here are the values I get in the code when I select the first date;

1Row = 0
(CDate(Me.CBDate)) = 6/26/2018
.Range("A2") =6/26/2018
xlFormulas = -4123
xlPart = 2
xlByRows = 1
xlNex = 1




Code

Private Sub FinishedButton1_Click()


Dim lRow As Long
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Meat Count")

On Error Resume Next
With ws
lRow = .UsedRange.Find(What:=(CDate(Me.CBDate)), After:=.Range("A2"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row

If lRow > 0 Then
.Cells(lRow, 2).Value = Me.Aspen_NYTextBox
.Cells(lRow, 3).Value = Me.Aspin_RibTextBox

Range("AC5").Select
Unload Me


Else
MsgBox Me.CBDate & " cannot be found"
End If
End With
On Error GoTo 0
End Sub

 

[AlphaFrog]

If you search for dates in Formulas (LookIn:=xlFormulas), you won't find a match. When you search Formulas, you are searching what's in the formula bar (=F1) and not the results of the formula.

So if you're searching cells with dates that are a result of formulas, search using xlValues and make sure your search-for date is the same format as the displayed dates.

 

[Torrez74]

Thanks, so I changed the "LookIn:=xlFormulas" expression to "LookIn:=xlValues" but I still get the same result; -4123

In addition my (CDate(Me.CBDate)) = "7/01/2018" while the .Range("A2") =6/26/2018. do I need to remove the ""? and instead of CDate should I change it to a numeric value?

 

[Torrez74]

Sorry, It works. I had toggled the debugger and was not seeing it complete.