Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help Please with Error 91message
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Help Please with Error 91message
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox, "hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Help Please with Error 91message
Hi Jim

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

Your Cells syntax is invalid. Perhaps you intend: Cells(27, "B")


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox, "hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Help Please with Error 91message
Thanks Norman
But I don't think it is the problem ...' b' is a variable used by the 'For
Next' loop representing a row number and 27 is a column number ....

Regards
Jim


"Norman Jones" wrote in message
...
Hi Jim

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

Your Cells syntax is invalid. Perhaps you intend: Cells(27, "B")


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox,
"hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Help Please with Error 91message
Hi Jim,

Firstly, my apologies. My initial response was, of course, nonsense.

Your error suggests that the contents of cells(b,27) are not being found.
You could trap this error using something like:

Set Rng = .Range("L2:L34").Find(.Cells(b, 27))

If Not Rng is Nothing Then
RowNo = Rng.Row
Else
MsgBox "No RowNo found"
'. Take appropriate action.
End if


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox, "hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Help Please with Error 91message
Thanks Norman ...

Ran your bit of code and message box displayed "No RowNo Found" for each of
the entries ....

However I know that they should be found ... since I have copied the cells
being tested from Column L to Colum AA ... i.e.Cells(b,27) etc

They are formatted as Time ... "hh:mm" ... Perhaps this has a bearing on the
problem

Any suggestions greatly appreciated

Regards and thanks again


"Norman Jones" wrote in message
...
Hi Jim,

Firstly, my apologies. My initial response was, of course, nonsense.

Your error suggests that the contents of cells(b,27) are not being found.
You could trap this error using something like:

Set Rng = .Range("L2:L34").Find(.Cells(b, 27))

If Not Rng is Nothing Then
RowNo = Rng.Row
Else
MsgBox "No RowNo found"
'. Take appropriate action.
End if


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox,
"hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton





Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default PS ... further developments
Norman,
If I format the both the columns to General, Number or Text then the cells
are found ... what is it about the Time format that is creating this problem
?
..... and ...
how can I work-around it ?

Regards
Jim


"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Thanks Norman ...

Ran your bit of code and message box displayed "No RowNo Found" for each
of the entries ....

However I know that they should be found ... since I have copied the cells
being tested from Column L to Colum AA ... i.e.Cells(b,27) etc

They are formatted as Time ... "hh:mm" ... Perhaps this has a bearing on
the problem

Any suggestions greatly appreciated

Regards and thanks again


"Norman Jones" wrote in message
...
Hi Jim,

Firstly, my apologies. My initial response was, of course, nonsense.

Your error suggests that the contents of cells(b,27) are not being found.
You could trap this error using something like:

Set Rng = .Range("L2:L34").Find(.Cells(b, 27))

If Not Rng is Nothing Then
RowNo = Rng.Row
Else
MsgBox "No RowNo found"
'. Take appropriate action.
End if


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox,
"hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton







Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default PS ... further developments
Hi Jim,

Try:

With Sheets("Sheet3")
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Range("n1"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91
MyTime = TimeValue(CDate(.Cells(b, 27).Value))
Set rng = .Range("L2:L34").Find(MyTime, LookIn:=xlFormulas)
'
If Not rng Is Nothing Then
rowno = rng.Row
Else
MsgBox "No RowNo found"
'Take appropriate action.
End If
If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(rowno + r, c) = .Cells(rowno + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvrowNo = .Range("L2:L34").Find(Format(TimeBox, "hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvrowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With

---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Norman,
If I format the both the columns to General, Number or Text then the cells
are found ... what is it about the Time format that is creating this
problem ?
.... and ...
how can I work-around it ?

Regards
Jim


"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Thanks Norman ...

Ran your bit of code and message box displayed "No RowNo Found" for each
of the entries ....

However I know that they should be found ... since I have copied the
cells being tested from Column L to Colum AA ... i.e.Cells(b,27) etc

They are formatted as Time ... "hh:mm" ... Perhaps this has a bearing on
the problem

Any suggestions greatly appreciated

Regards and thanks again


"Norman Jones" wrote in message
...
Hi Jim,

Firstly, my apologies. My initial response was, of course, nonsense.

Your error suggests that the contents of cells(b,27) are not being
found. You could trap this error using something like:

Set Rng = .Range("L2:L34").Find(.Cells(b, 27))

If Not Rng is Nothing Then
RowNo = Rng.Row
Else
MsgBox "No RowNo found"
'. Take appropriate action.
End if


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b,
27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox,
"hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton









Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9
Default Great Stuff

Norman .... Thank you very much ... works a treat

Regards and forever indebted

Jim

"Norman Jones" wrote in message
...
Hi Jim,

Try:

With Sheets("Sheet3")
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Range("n1"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91
MyTime = TimeValue(CDate(.Cells(b, 27).Value))
Set rng = .Range("L2:L34").Find(MyTime, LookIn:=xlFormulas)
'
If Not rng Is Nothing Then
rowno = rng.Row
Else
MsgBox "No RowNo found"
'Take appropriate action.
End If
If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(rowno + r, c) = .Cells(rowno + r, c) + .Cells(b, 27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvrowNo = .Range("L2:L34").Find(Format(TimeBox, "hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvrowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With

---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Norman,
If I format the both the columns to General, Number or Text then the
cells are found ... what is it about the Time format that is creating
this problem ?
.... and ...
how can I work-around it ?

Regards
Jim


"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Thanks Norman ...

Ran your bit of code and message box displayed "No RowNo Found" for each
of the entries ....

However I know that they should be found ... since I have copied the
cells being tested from Column L to Colum AA ... i.e.Cells(b,27) etc

They are formatted as Time ... "hh:mm" ... Perhaps this has a bearing on
the problem

Any suggestions greatly appreciated

Regards and thanks again


"Norman Jones" wrote in message
...
Hi Jim,

Firstly, my apologies. My initial response was, of course, nonsense.

Your error suggests that the contents of cells(b,27) are not being
found. You could trap this error using something like:

Set Rng = .Range("L2:L34").Find(.Cells(b, 27))

If Not Rng is Nothing Then
RowNo = Rng.Row
Else
MsgBox "No RowNo found"
'. Take appropriate action.
End if


---
Regards,
Norman



"sa3214 @eclipse.co.uk" <jimburton<REMOVE wrote in message
...
Can anyone tell me where I am going wrong with this code:

With Sheets(SheetName)
.Range("M2:U34").ClearContents
.Range("AA2:AC65536").ClearContents
.Range("V2") = Format(DateBox, "Long Date")
.Columns("A:J").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=.Range( _
"V1:V2"), CopyToRange:=.Columns("AA:AC"), Unique:=False

LastBooking = .Range("AA65536").End(xlUp).Row

For b = 2 To LastBooking

' .... NEXT LINE causes Error 91

RowNo = .Range("L2:L34").Find(.Cells(b, 27)).Row

If .Cells(b, 28) = "Yes" Then
c = 17
Else
c = 14
End If
For r = 0 To 7
.Cells(RowNo + r, c) = .Cells(RowNo + r, c) + .Cells(b,
27)
Next r
Next b

If TimeBox < "" And Seats < "" Then
ProvRowNo = .Range("L2:L34").Find(Format(TimeBox,
"hh:mm")).Row
If Smoking = True Then
c = 16
Else
c = 13
End If
For r = 0 To 7
.Cells(ProvRowNo + r, c) = Format(Seats, "##0")
Next r
End If
End With



Regards and TIA
Jim Burton











Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Visual Basic Error Run Time Error, Type Mismatch Meg Partridge Excel Discussion (Misc queries) 12 September 10th 08 06:10 PM
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
Automation Error, Unknown Error. Error value - 440 Neo[_2_] Excel Programming 0 May 29th 04 05:26 AM
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) Tim[_36_] Excel Programming 4 April 23rd 04 02:53 AM


All times are GMT +1. The time now is 02:19 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement