Using Output of CDate function to copy and paste date in another workbook

[KiwiGrue]

I have developed a macro to seek user input on the date of the month for data to be extracted from one worksheet to be pasted to another. Once I have pasted the relevant data to the correct cells/columns I need to provide a date in column A for each new row.

The macro I have is ...

Sub DateInputBox()
Dim dte As Date

mbox = InputBox("Enter date of month ending", "Month oif Data")

If IsDate(mbox) Then
dte = CDate(mbox)

Else
MsgBox "This is not a date! Try again."

End If

Debug.Print dte

End Sub

I have tried various options unsuccessfully to access the date to copy and paste it in the other workbook in column A (this macro is embedded rather than called in the main subroutine with the destination being ... cells(irTarget+2,1).select ...).

Any assistance and pointers would be appreciated.

Cheers
Alistair

 

[Joe4]

Your code looks a bit curious to me, especially this part:

If IsDate(mbox) Then
    dte = CDate(mbox)
Else
    MsgBox "This is not a date! Try again."
End If

The CDate function converts a text value to a date value and the IsDate function checks to see if a value is a date.
If a value is already a date, there is no need to use the CDate function on it.

So I don't think you need that part in your code. I think you can modify your check like this:

    If Not IsDate(mbox) Then
        MsgBox "This is not a date! Try again."
        Exit Sub
    End If

You also want that "Exit Sub" in there so it quits the macro and doesn't continue on and perform the rest of the code.

However, it seems like you may have other issues/questions about your problem, though they are not that clear from your post.
If that is the case, can you describe these other issues in more detail?

 

[Marc L]

If a value is already a date, there is no need to use the CDate function on it.

True but wrong ! As it may depend on the Regional settings like on my side without CDate function Excel so badly converts the text as a date​

'cause by default VBA understands only text dates as US format !​

 

[Joe4]

True but wrong ! As it may depend on the Regional settings like on my side without CDate function Excel so badly converts the text as a date'cause by default VBA understands only text dates as UK / US format !

Ah, OK. Not a problem for me on this side of the pond.

 

[KiwiGrue]

Ah, OK. Not a problem for me on this side of the pond.

Ah okay thanks guys maybe I was not clear enough ... once the user has input the date via the inputbox how do I 'access' it and paste it into another worksheet range? Joe4 I shall try your suggested code to see if that gives the correct regional date format in New Zealand. Cheers

 

[Joe4]

once the user has input the date via the inputbox how do I 'access' it and paste it into another worksheet range?

If you wanted to put it in cell A10, you would just put the following at the end of the code you currently have:

Range("A10") = dte

Is that what you were looking for?

 

[KiwiGrue]

If you wanted to put it in cell A10, you would just put the following at the end of the code you currently have:

Range("A10") = dte

Is that what you were looking for?

Many thanks it pointed me in the right direction ... I am pasting in another active worksheet and amended code ... Cells(irTarget + 2, 1) = dte ... does the job and is dynamic.

Cheers
Alistair