help with type mismatch error

  • Thread starter Thread starter Jin
  • Start date Start date
J

Jin

I need a help with the below procedure, I get a Type Mismatch Error when the
code reaches the datevalue. Please help.

Thanks,

Jin


Sub Consolidate()

Dim BaseBook As Workbook
Dim i As Integer
Dim j As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\SALES"
.SearchSubFolders = True
myStoreString = InputBox("Store Number?")
.Filename = "***" & myStoreString & "**"
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then

Set BaseBook = Workbooks.Open(.FoundFiles(1), UpdateLinks:=0)

BaseBook.Worksheets(1).Name = Left(BaseBook.Name, 3)

For i = 2 To .FoundFiles.Count
Set myBook = Workbooks.Open(.FoundFiles(i), UpdateLinks:=0)
myFilename = myBook.Name
myBook.Worksheets(1).Move After:=BaseBook.Sheets(i - 1)
ActiveSheet.Name = Left(myFilename, 3)
Next i

For j = 1 To BaseBook.Worksheets.Count
For i = j To BaseBook.Worksheets.Count
If DateValue(Worksheets(i).Name & " 3, 2003") < _
DateValue(Worksheets(j).Name & "3, 2003") Then
Worksheets(i).Move Before:=Worksheets(j)
End If
Next i
Next j

BaseBook.SaveAs Application.GetSaveAsFilename _
("CA" & myStoreString & "sls03" & ".xls")
End If

End With

End Sub
 
If DateValue(Worksheets(i).Name & " 3, 2003") < _
DateValue(Worksheets(j).Name & "3, 2003") Then
Worksheets(i).Move Before:=Worksheets(j)
Click to expand...

There is a space before the "3" in the first line, and but none in the
second, but that may not be the problem.

But if I guess what you are trying to do, the error might come from
trying to derive a datevalue of a wrong date format. Is
worksheet(1).name or worksheet(j).name an integer? Then the Datevalue
code should be

DateValue(""" & Worksheets(i).Name & "/3/2003"")
DateValue(""" & Worksheets(j).Name & "/3/2003"")

On a worksheet I tried, =DATEVALUE("January 3, 2003") doesn't work,
but =DATEVALUE("1/3/2003") does. Note that the date has to be a text
string. The triple double quotes at the beginning is a way to have VBA
write a " rather than just consider it as the start of a string. I
think you need three of them -- please check if it's 4 double quotes.
 
Back
Top