Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with type mismatch error
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 Befo=Worksheets(j) End If Next i Next j BaseBook.SaveAs Application.GetSaveAsFilename _ ("CA" & myStoreString & "sls03" & ".xls") End If End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with type mismatch error
If DateValue(Worksheets(i).Name & " 3, 2003") < _
DateValue(Worksheets(j).Name & "3, 2003") Then Worksheets(i).Move Befo=Worksheets(j) 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. "Jin" wrote in message om... 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 Befo=Worksheets(j) End If Next i Next j BaseBook.SaveAs Application.GetSaveAsFilename _ ("CA" & myStoreString & "sls03" & ".xls") End If End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
Type Mismatch Error | Excel Discussion (Misc queries) | |||
Type Mismatch Error - Help Please | Excel Worksheet Functions | |||
Type Mismatch Error | Excel Discussion (Misc queries) | |||
Type Mismatch Error Need Help | Excel Programming |