VBA written in 2003 failing in 97

[kraljb]

I am wondering if someone can help me, I am having the following code work just fine in 2003 and fail miserably when I try to run it on a PC with 97.
It fails on this line: lDateCol = .Cells.Find("DateRange").Column
The error is: "Run-Time error '91': Object variable or With block variable not set"
Is this just a version issue with incompatability between 97 and 2003, or is this something else?

Here is my Code...

Function OnlyValidDays()
Dim dStart As Date
Dim dEnd As Date
Dim rngDate As Range
Dim CurrCell As Range
Dim HideRange As Range
Dim ShowRange As Range
Dim lDateCol As Long
Dim lDate_Start_Row As Long
Dim lDate_End_Row As Long
Dim lFindSummary As Long
Dim wb As Workbook
Dim ws As Worksheet
'WriteEvent "Module1:OnlyValidDays Begin"
Set wb = ActiveWorkbook()
Set ws = wb.ActiveSheet
With ws
lDateCol = .Cells.Find("DateRange").Column
lDate_Start_Row = .Cells.Find("DateRange").Row + 1
Set HideRange = .Cells.Find("DateRange").EntireRow
Set ShowRange = .Cells.Find("Total").EntireRow
lDate_End_Row = .Cells.Find("*", , , xlRows, , xlPrevious).Row
Set rngDate = Range(.Cells(lDate_Start_Row, lDateCol), .Cells(lDate_End_Row, lDateCol))
If IsDate(.Cells(lDate_Start_Row - 3, lDateCol).Value) Then
dStart = .Cells(lDate_Start_Row - 3, lDateCol).Value
If IsDate(.Cells(lDate_Start_Row - 2, lDateCol).Value) Then
dEnd = .Cells(lDate_Start_Row - 2, lDateCol).Value
'WriteEvent "Module1:OnlyValidDays Range Cycle:" & rngDate.Address
For Each CurrCell In rngDate
With CurrCell
If IsDate(.Value) Then
If (.Value >= dStart) And (.Value <= dEnd) Then
'WriteEvent "Module1:OnlyValidDays Date:" & .Value & " Start:" & dStart & " End:" & dEnd
If .EntireRow.Hidden Then
lFindSummary = 0
Do While .Offset(lFindSummary, 0).EntireRow.Summary = False
lFindSummary = lFindSummary + 1
Loop
If .Offset(lFindSummary, 0).EntireRow.ShowDetail Then
Set ShowRange = Union(ShowRange, .EntireRow)
End If
End If
Else
'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value & " EntireRow.Hidden:" & .EntireRow.Hidden
If Not .EntireRow.Hidden Then
'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value & " Changing to Hidden:" & .EntireRow.Address
Set HideRange = Union(HideRange, .EntireRow)
End If
End If
End If
'WriteEvent "Module1:OnlyValidDays CurrentRow:" & .EntireRow.Address & " Hidden:" & .EntireRow.Hidden
End With
Next
End If
End If
End With
ShowRange.EntireRow.Hidden = False
HideRange.EntireRow.Hidden = True
'WriteEvent "Module1:OnlyValidDays End"
End Function

[Norman Jones]

Hi Kraljb,

The line

> lDateCol = .Cells.Find("DateRange").Column

will produce your encountered error if the search expression "DateRange"
does not exist on the sheet.

Try adding an appropriate On Error handler to catch the error.

---
Regards,
Norman



"kraljb" <kraljb.1sugxp_1122494963.6045@excelforum-nospam.com> wrote in
message news:kraljb.1sugxp_1122494963.6045@excelforum-nospam.com...
>
> I am wondering if someone can help me, I am having the following code
> work just fine in 2003 and fail miserably when I try to run it on a PC
> with 97.
> It fails on this line: lDateCol = .Cells.Find("DateRange").Column
> The error is: "Run-Time error '91': Object variable or With block
> variable not set"
> Is this just a version issue with incompatability between 97 and 2003,
> or is this something else?
>
> Here is my Code...
>
> Function OnlyValidDays()
> Dim dStart As Date
> Dim dEnd As Date
> Dim rngDate As Range
> Dim CurrCell As Range
> Dim HideRange As Range
> Dim ShowRange As Range
> Dim lDateCol As Long
> Dim lDate_Start_Row As Long
> Dim lDate_End_Row As Long
> Dim lFindSummary As Long
> Dim wb As Workbook
> Dim ws As Worksheet
> 'WriteEvent "Module1:OnlyValidDays Begin"
> Set wb = ActiveWorkbook()
> Set ws = wb.ActiveSheet
> With ws
> lDateCol = .Cells.Find("DateRange").Column
> lDate_Start_Row = .Cells.Find("DateRange").Row + 1
> Set HideRange = .Cells.Find("DateRange").EntireRow
> Set ShowRange = .Cells.Find("Total").EntireRow
> lDate_End_Row = .Cells.Find("*", , , xlRows, , xlPrevious).Row
> Set rngDate = Range(.Cells(lDate_Start_Row, lDateCol),
> Cells(lDate_End_Row, lDateCol))
> If IsDate(.Cells(lDate_Start_Row - 3, lDateCol).Value) Then
> dStart = .Cells(lDate_Start_Row - 3, lDateCol).Value
> If IsDate(.Cells(lDate_Start_Row - 2, lDateCol).Value) Then
> dEnd = .Cells(lDate_Start_Row - 2, lDateCol).Value
> 'WriteEvent "Module1:OnlyValidDays Range Cycle:" &
> rngDate.Address
> For Each CurrCell In rngDate
> With CurrCell
> If IsDate(.Value) Then
> If (.Value >= dStart) And (.Value <= dEnd) Then
> 'WriteEvent "Module1:OnlyValidDays Date:" & .Value & "
> Start:" & dStart & " End:" & dEnd
> If .EntireRow.Hidden Then
> lFindSummary = 0
> Do While .Offset(lFindSummary, 0).EntireRow.Summary =
> False
> lFindSummary = lFindSummary + 1
> Loop
> If .Offset(lFindSummary, 0).EntireRow.ShowDetail
> Then
> Set ShowRange = Union(ShowRange, .EntireRow)
> End If
> End If
> Else
> 'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value &
> " EntireRow.Hidden:" & .EntireRow.Hidden
> If Not .EntireRow.Hidden Then
> 'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value
> & " Changing to Hidden:" & .EntireRow.Address
> Set HideRange = Union(HideRange, .EntireRow)
> End If
> End If
> End If
> 'WriteEvent "Module1:OnlyValidDays CurrentRow:" &
> EntireRow.Address & " Hidden:" & .EntireRow.Hidden
> End With
> Next
> End If
> End If
> End With
> ShowRange.EntireRow.Hidden = False
> HideRange.EntireRow.Hidden = True
> 'WriteEvent "Module1:OnlyValidDays End"
> End Function
>
>
> --
> kraljb
> ------------------------------------------------------------------------
> kraljb's Profile:
> http://www.excelforum.com/member.php...fo&userid=9955
> View this thread: http://www.excelforum.com/showthread...hreadid=390721
>

[Jim Rech]

I find that this line of code runs the same in 2003 as 97:

MsgBox Cells.Find("DateRange").Column

Meaning that it works if it finds the string else it generates the error
message you reported (which can and should be trapped as Norman said).

--
Jim
"kraljb" <kraljb.1sugxp_1122494963.6045@excelforum-nospam.com> wrote in
message news:kraljb.1sugxp_1122494963.6045@excelforum-nospam.com...
|
| I am wondering if someone can help me, I am having the following code
| work just fine in 2003 and fail miserably when I try to run it on a PC
| with 97.
| It fails on this line: lDateCol = .Cells.Find("DateRange").Column
| The error is: "Run-Time error '91': Object variable or With block
| variable not set"
| Is this just a version issue with incompatability between 97 and 2003,
| or is this something else?
|
| Here is my Code...
|
| Function OnlyValidDays()
| Dim dStart As Date
| Dim dEnd As Date
| Dim rngDate As Range
| Dim CurrCell As Range
| Dim HideRange As Range
| Dim ShowRange As Range
| Dim lDateCol As Long
| Dim lDate_Start_Row As Long
| Dim lDate_End_Row As Long
| Dim lFindSummary As Long
| Dim wb As Workbook
| Dim ws As Worksheet
| 'WriteEvent "Module1:OnlyValidDays Begin"
| Set wb = ActiveWorkbook()
| Set ws = wb.ActiveSheet
| With ws
| lDateCol = .Cells.Find("DateRange").Column
| lDate_Start_Row = .Cells.Find("DateRange").Row + 1
| Set HideRange = .Cells.Find("DateRange").EntireRow
| Set ShowRange = .Cells.Find("Total").EntireRow
| lDate_End_Row = .Cells.Find("*", , , xlRows, , xlPrevious).Row
| Set rngDate = Range(.Cells(lDate_Start_Row, lDateCol),
| Cells(lDate_End_Row, lDateCol))
| If IsDate(.Cells(lDate_Start_Row - 3, lDateCol).Value) Then
| dStart = .Cells(lDate_Start_Row - 3, lDateCol).Value
| If IsDate(.Cells(lDate_Start_Row - 2, lDateCol).Value) Then
| dEnd = .Cells(lDate_Start_Row - 2, lDateCol).Value
| 'WriteEvent "Module1:OnlyValidDays Range Cycle:" &
| rngDate.Address
| For Each CurrCell In rngDate
| With CurrCell
| If IsDate(.Value) Then
| If (.Value >= dStart) And (.Value <= dEnd) Then
| 'WriteEvent "Module1:OnlyValidDays Date:" & .Value & "
| Start:" & dStart & " End:" & dEnd
| If .EntireRow.Hidden Then
| lFindSummary = 0
| Do While .Offset(lFindSummary, 0).EntireRow.Summary =
| False
| lFindSummary = lFindSummary + 1
| Loop
| If .Offset(lFindSummary, 0).EntireRow.ShowDetail
| Then
| Set ShowRange = Union(ShowRange, .EntireRow)
| End If
| End If
| Else
| 'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value &
| " EntireRow.Hidden:" & .EntireRow.Hidden
| If Not .EntireRow.Hidden Then
| 'WriteEvent "Module1:OnlyValidDays BadDate:" & .Value
| & " Changing to Hidden:" & .EntireRow.Address
| Set HideRange = Union(HideRange, .EntireRow)
| End If
| End If
| End If
| 'WriteEvent "Module1:OnlyValidDays CurrentRow:" &
| EntireRow.Address & " Hidden:" & .EntireRow.Hidden
| End With
| Next
| End If
| End If
| End With
| ShowRange.EntireRow.Hidden = False
| HideRange.EntireRow.Hidden = True
| 'WriteEvent "Module1:OnlyValidDays End"
| End Function
|
|
| --
| kraljb
| ------------------------------------------------------------------------
| kraljb's Profile:
http://www.excelforum.com/member.php...fo&userid=9955
| View this thread: http://www.excelforum.com/showthread...hreadid=390721
|