Subscript out of Range
 

Hi,

Where the **** is below I get the above error message,
why should this be?



Option Explicit
Sub testme()

Dim myFile As String
Dim myPath As String
Dim prevWkbk As Workbook
Dim curWkbk As Workbook
Dim fDate As Date
Dim KeepThisFileName As String
Dim KeepThisDate As Date
Dim i As Long
Dim j As Long

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'Set curWkbk = ActiveWorkbook '?
'or
*****Set curWkbk = Workbooks("wip " & Format(Date, "dd-mm-yy") & ".xls")


'get the list of files
KeepThisFileName = ""
Do While myFile < ""
If LCase(myFile) Like "wip ##-##-##.xls" Then
fDate = DateSerial(2000 + Mid(myFile, 11, 2), _
Mid(myFile, 8, 2), _
Mid(myFile, 5, 2))
If fDate = Date Then
'skip today's file
Else
If KeepThisFileName = "" Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
If fDate KeepThisDate Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
End If
End If
myFile = Dir()
Loop

If KeepThisFileName = "" Then
MsgBox "no file found"
Exit Sub
End If

Set prevWkbk = Workbooks.Open(Filename:=myPath & KeepThisFileName)
For i = 1 To 100
For j = 1 To 100
If curWkbk.Worksheets("Jobs").Cells(i, j) _
< prevWkbk.Worksheets("Jobs").Cells(i, j) Then
'do what you will
Else
'do what you won't
End If
Next j
Next i

End Sub

Subscript out of Range
 

Because you don't have a workbook open with that name.

Same as the first time you posted this question.

Dave did offer you an alternative

Set curWkbk = ActiveWorkbook

use that instead.

--
Regards,
Tom Ogilvy


"teresa" wrote in message
...
Hi,

Where the **** is below I get the above error message,
why should this be?



Option Explicit
Sub testme()

Dim myFile As String
Dim myPath As String
Dim prevWkbk As Workbook
Dim curWkbk As Workbook
Dim fDate As Date
Dim KeepThisFileName As String
Dim KeepThisDate As Date
Dim i As Long
Dim j As Long

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) < "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

'Set curWkbk = ActiveWorkbook '?
'or
*****Set curWkbk = Workbooks("wip " & Format(Date, "dd-mm-yy") &
".xls")


'get the list of files
KeepThisFileName = ""
Do While myFile < ""
If LCase(myFile) Like "wip ##-##-##.xls" Then
fDate = DateSerial(2000 + Mid(myFile, 11, 2), _
Mid(myFile, 8, 2), _
Mid(myFile, 5, 2))
If fDate = Date Then
'skip today's file
Else
If KeepThisFileName = "" Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
If fDate KeepThisDate Then
KeepThisFileName = myFile
KeepThisDate = fDate
End If
End If
End If
myFile = Dir()
Loop

If KeepThisFileName = "" Then
MsgBox "no file found"
Exit Sub
End If

Set prevWkbk = Workbooks.Open(Filename:=myPath & KeepThisFileName)
For i = 1 To 100
For j = 1 To 100
If curWkbk.Worksheets("Jobs").Cells(i, j) _
< prevWkbk.Worksheets("Jobs").Cells(i, j) Then
'do what you will
Else
'do what you won't
End If
Next j
Next i

End Sub

Subscript out of Range
 

Teresa,
It looks as though you have left the '&' on the end of that line of
code with nothing to concatenate to. Give that a try--Lonnie M.

Subscript out of Range
 

*****Set curWkbk = Workbooks("wip " & Format(Date, "dd-mm-yy") & ".xls")

Where's that?

--
Regards,
Tom Ogilvy

"Lonnie M." wrote in message
oups.com...
Teresa,
It looks as though you have left the '&' on the end of that line of
code with nothing to concatenate to. Give that a try--Lonnie M.

Subscript out of Range
 

My display wasn't wrapping her text, it was covered by the sponsored
links.
Disregard... I'm a dope...