Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA written in 2003 failing in 97
I am wondering if someone can help me, I am having the following cod work just fine in 2003 and fail miserably when I try to run it on a P with 97. It fails on this line: lDateCol = .Cells.Find("DateRange").Column The error is: "Run-Time error '91': Object variable or With bloc 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.ShowDetai 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:" & .Valu & " 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 Functio -- kralj ----------------------------------------------------------------------- kraljb's Profile: http://www.excelforum.com/member.php...nfo&userid=995 View this thread: http://www.excelforum.com/showthread.php?threadid=39072 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA written in 2003 failing in 97
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA written in 2003 failing in 97
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" wrote in message ... | | 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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros written in Excel 97 will not work in Excel 2003 | Excel Programming | |||
2003 Shared Worksheet Protection Settings Failing For My 2000 User | Excel Worksheet Functions | |||
can i convert numbers to written text in excell 2003? | Excel Discussion (Misc queries) | |||
Excel 2003 wont recognize UDF written with 2000 | Excel Programming | |||
Macro written in Excel 2003 and saved as Excel 2000 .xls | Excel Programming |