Type mismatch

[SamS]

I have a header row that contains text and dates, the dates are formatted as Sep-02 etc. and am using an input box to loop through each cell till I find the correct date eg
Item Descr Qty Jul-02 Aug-02 Sep-02

The code I was using is something like this:

Dim Month as Date
Month = InputBox("Please enter the month eg Sep-02")

Range("A2").Select

Do While ActiveCell.Value <> Month
ActiveCell.Offset(0, 1).Select
Loop

This gives an error message on the first cell as it is Text and not a Date. In the past I've usually had the header formatted as text and been able to work around the problem but in this case it is not an option to change the format.

I hate working with dates.

 

[phantom1975]

Try using data type String or Variant.

 

[SamS]

I tried both but neither works - both bypass the error but neither recognises the date

 

[phantom1975]

Have you tried running your code by using F8 in VBE so you can see step-by-step what is going on and when/where the error occurs? If so, at what point do you get the error?

 

[SamS]

If I Dim variable as a Date the error occurs on the first Text entry, if I leave as a Variant or set it as a String the variable shows the Date Field as 01/09/2002 and the loop continues on until the end of 256 columns.

This has really driven me batty.

 

[phantom1975]

The problem is that if you Dim the variable as a DATE, but the data is only month and year, then you will get an error. Although the dates in the columns may APPEAR 9/20/2002, the value is quite different. I would suggest that you first try having the user input the date in a standard date format (e.g., 9/20/2002). Then Dim your variable as Date. See if that works. If so, you can then try tweaking as you go.

Does that help?

 

[SamS]

On 2002-11-04 18:02, phantom1975 wrote:
The problem is that if you Dim the variable as a DATE, but the data is only month and year, then you will get an error. Although the dates in the columns may APPEAR 9/20/2002, the value is quite different. I would suggest that you first try having the user input the date in a standard date format (e.g., 9/20/2002). Then Dim your variable as Date. See if that works. If so, you can then try tweaking as you go.

Does that help?

The dates are entered as 01/09/2002 and the cells are formatted as Sep-02. If I Dim the variable as Date errors occur as soon as the first Text field is found. Unfortunately the header row is a mixture of Text and Dates. I had tried inputting 01/09/2002 with the variable set as String but still no go. Think I've tried every combination.

 

[phantom1975]

If you want to email it to me, I'll take a crack at it.

william.rowe@alltel.com

 

[SamS]

Done

 

[phantom1975]

Got it. I'm gonna look at it right now.