|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 08:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com