Help Please with Error 91message

[sa3214]

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

 

[Norman Jones]

Hi Jim

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

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

 

[sa3214]

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]

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

 

[sa3214]

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

 

[sa3214]

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

 

[Norman Jones]

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

 

[sa3214]

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

Regards and forever indebted

Jim