PDA

View Full Version : [SOLVED] Searching for date in array



Chunk
09-29-2016, 08:37 AM
For lack of being able to describe the root problem, here is what I want the user to be able to do:

From a userform

Select a project in a combobox

Depending on the project selected, the user will have a choice of dates, via another combobox (that come from a project worksheet, in the 'D' column staring at D3(variable number of dates per project))

When the date is selected, data from the worksheet will be added to the 25 or so textboxes on the userform.


I can get the project combobox to work with the date combobox.

The problem I am having is getting the date combobox to properly search the worksheet for the date, so I can use that position to populate the text boxes.

Here is the code I am using:

Private Sub cb_DDate_Change()
cb_DDate = Format(cb_DDate, "mm/dd/yyyy")
Call mod_DataEntry.DateChange

End Sub




Sub DateChange()

Dim ws
Dim Rng As Long
Dim FindString As Long

Set ws = ThisWorkbook.Worksheets(UF_Main.cbo_project.Value)



FindString = UF_Main.cb_DDate.Value



If Trim(FindString) <> "" Then
Set Rng = ws.Cells.Find( _
What:=FindString, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

UF_Main.tb_PercentDone.Text = Rng(1, 9)

End If


End Sub

I am receiving an Object Required error.

My forehead is starting to ache from the desk slamming. Please help.

Thanks in advance for your time.

Chunk

mikerickson
09-29-2016, 08:40 AM
The problem that I see is that the .Find is looking for a string when the contents of the cell are a date.


Try

Set Rng = ws.Cells.Find(What:=CDate(FindString), ...)

Chunk
09-29-2016, 08:44 AM
Mike,
added that in, but am still receiving the 'Object Required' error

Chunk

Chunk
09-29-2016, 08:46 AM
Here is the cade I am using to populate the date combobox


Sub ProjChange()
Dim ws
Dim Rng As Range
Dim FindString As String



Set ws = ThisWorkbook.Worksheets(UF_Main.cbo_project.Value)

Worksheets(UF_Main.cbo_project.Text).Activate

Range("D3", Range("D" & Rows.Count).End(xlUp)).Name = "Dynamic"

UF_Main.cb_DDate.RowSource = "Dynamic"

FindString = UF_Main.cbo_project.Value

If Trim(FindString) <> "" Then
With Sheets("LookUpList").Range("A2:A30")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
UF_Main.cb_AvlType.Text = Rng(1, 3)


End If
End With
End If



End Sub

Chunk
09-29-2016, 10:03 AM
Got it to work,

Here is the code that worked:


Sub DateChange()

Dim ws
Dim Rng As Range
Dim FindString As String

Set ws = ThisWorkbook.Worksheets(UF_Main.cbo_project.Value)



FindString = UF_Main.cb_DDate.Value



If Trim(FindString) <> "" Then
Set Rng = ws.Cells.Find( _
What:=CDate(FindString), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

UF_Main.tb_PercentDone.Text = Rng(1, 9)


End If


End Sub

Changed Rng to Range and Findstring to String