Convert Text Box string to date (VBA Excel 2003)

[DeusXv]

I'm wondering how you convert a string that a user enters into a text box to a date

so if I'm using this line of code to take the users input

 If .Value = CDate(DateSearch.TextBox1.Text) Then
                    .Offset(0, 1).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

I want the text that they enter (01/01/2012) to be converted to a date so that it can be compared to other dates.

Thanks In advance

- DeusXv

 

[VoG]

Try

If .Value = DateValue(DateSearch.TextBox1.Text) Then

 

[DeusXv]

Yeah I've tried that however what happens is it only copies one of the dates

So if the user enters the date 01/2001 they then want the code to copy all dates that have that month and year so if i have the dates

01/01/2001
02/01/2001
03/01/2001

The code only copies the first date and pastes it to sheet2 and not the other dates. looking around people said that converting the string that they enter to a date would allow me to look up month values and year values.

Do I maybe need to format the textbox as I have no lines of code for the textbox in the userform.

 

[Richard Schollar]

Your two messages look to me like two different questions! You have the answer to the first, but please could you expand on what you require in the second?

Please be clear about what the user is inputting and what the output you require is and how the output should flow from the input.

 

[DeusXv]

So this is the basic Idea behind the code

For example:

01/03/2013	10
23/04/2013	2
04/04/2013	12
09/01/2013	12

<tbody>
</tbody>

User inputs '04/2013' and the code searches through the above list of dates and returns the value 14.

What I have read is that I need to convert what the user inputs to a date so that I can compare it to the list of dates and then copy what matches the criteria that they have typed in the textbox.

 

[DeusXv]

Here is the full code that I'm using so you can see how I've attempted to go about the above.

Sub SearchMacro()
DateSearch.Show


Dim LR As Long, i As Long
  With Sheets("Sheet1")
       LR = .Range("A" & Rows.Count).End(xlUp).Row
        For i = 1 To LR
            With .Range("A" & i)
                If .Value = DateValue(DateSearch.TextBox1.Text) Then
                    .Offset(0, 1).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
                End If
            End With
        Next i
    End With


If DateSearch.TextBox1.Text = ("01/2001") Then
Worksheets("Sheet2").Range("B3") = Application.Sum(Sheets("Sheet2").Columns("A:A"))


ElseIf DateSearch.TextBox1 = ("02/2001") Then
Worksheets("Sheet2").Range("C3") = Application.Sum(Sheets("Sheet2").Columns("A:A"))

End if

End Sub

The if's that I added at the end are just to decide where the adding of the dates goes depending on the month that they have selected.

Am I trying to tackle this is a real backwards fashion or is there a far simpler way to tackle this issue ?