• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Booking System

sgmpatnaik

sgmpatnaik

Active Member
Hi all

Good Afternoon

i need some help to create a code for booking system that is i want to check the Check in Date and check out date, i have one code which is working for single criteria but when i try to use 2 criteria then i got a error Msg for Type MisMatch the code is

Code: 
Sub PostBooking()

    Dim msg As String, Ans As Variant
    Dim iRow As Long, iCol As Long
    Dim inDate As Date, outDate As Date
    
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"

    With Sheet1.Range("C37")
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
    
        Select Case .Value
            Case Is = "Available" 'OK to place booking
                With Sheet5 'Booking Status
                Dim lCell As Range
                Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                lCell.Offset(0, -2).Value = Sheet1.Range("C20").Value 'Check In Date
                lCell.Offset(0, -1).Value = Sheet1.Range("G20").Value 'Check Out Date
                lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
                
            End With
            
            'show the room as booked on the Dates Tab
                With Sheet3
                iRow = Application.WorksheetFunction.Match(Sheet1.[inDate] & [outDate], Range("Dates"), 0)
                iCol = Application.WorksheetFunction.Match(Sheet1.[C31], Range("Function_Halls"), 0)
                iRow = iRow + 7 'adjust for header
                iCol = iCol + 1 'adjust for header
                .Cells(iRow, iCol).Value = "Booked"
                End With
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
    End With
 
End Sub

The error code is

Code: 
iRow = Application.WorksheetFunction.Match(Sheet1.[inDate] & [outDate], Range("Dates"), 0)

your replay is highly appreciate


Thanking You

With Regards

SP
 
I think the problem traces back to this bit:
Code: 
    With Sheet1.Range("C37")
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
Note that your With statement refers to a range, and then the two variables are set to a range that traces back to that With statement. The C20 referenced here is not the cell called C20 in your worksheet. It's what would be called C20 if cell C37 is used as starting reference point (aka, A1). This notation "Range("C37").Range("C20")" will end up giving you the reference to E56.

Next, your problem line itself, I'm not sure what you're trying to do. This bit:
Sheet1.[inDate] & [outDate]
Doesn't make any sense. I think the "Sheet1" portion needs to be taken out. Remember that inDate and outDate are values, not cells, so you can't use them as a cell reference. Are you trying to find the inDate, or the outDate within the "Dates" range? Also, do you know for sure that those values will be found?
 
@Luke M

Thanks for your replay

actually in Sheet1 Range ("C37") there i mention as "Booked","Available" with Reference the D37

in D37 i use the formula as
Code: 
=IFERROR(INDEX(Booking_Data,MATCH($C$20,Dates,0),MATCH($C$31,Function_Halls,0)),"")

and C20 has a Check in Date
G20 has a Checkout Date

actually the code is works with original code that is

Code: 
Sub PostBooking()

    Dim msg As String, Ans As Variant
    Dim iRow As Long, iCol As Long
    
    
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"

    With Sheet1.Range("C37")
    
        Select Case .Value
            Case Is = "Available" 'OK to place booking
                With Sheet5 'Booking Status
                Dim lCell As Range
                Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                lCell.Offset(0, -2).Value = Sheet1.Range("C20").Value 'Check In Date
                lCell.Offset(0, -1).Value = Sheet1.Range("G20").Value 'Check Out Date
                lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
                
            End With
            
            'show the room as booked on the Dates Tab
                With Sheet3
                iRow = Application.WorksheetFunction.Match(Sheet1.[C20], Range("Dates"), 0)
                iCol = Application.WorksheetFunction.Match(Sheet1.[C31], Range("Function_Halls"), 0)
                iRow = iRow + 7 'adjust for header
                iCol = iCol + 1 'adjust for header
                .Cells(iRow, iCol).Value = "Booked"
                End With
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
    End With
 
End Sub

i have a separate Tab named as Dates, in this tab i have some headers for room and dates, when i run the code then it display as booked in the Check date Range ("C20") but i want to fill the Booked in Check in Date to Checkout Date

Say if a person book a room from 10/10/2013 to 15/10/2013 for a room then it fill in the date 10/10/2013 as Booked, if i gave again in Check in Date as 11/10/2013 then it will show me as Available for that room so i want to change the code

Thanking You

With Regards

SP
 
First, let's change your formula that's checking availability to:
=IFERROR(IF(COUNTIF(INDEX(Booking_Data,MATCH($C$20,Dates,0),MATCH($C$31,Function_Halls,0)):INDEX(Booking_Data,MATCH($G$20,Dates,0),MATCH($C$31,Function_Halls,0)),"Booked"),"Booked","Available"),"")

The COUNTIF function now looks at the whole date range and sees if there's any bookings in that time slot.

To adjust the macro now...
Code: 
Sub PostBooking()
 
    Dim msg As String, Ans As Variant
    Dim iRow As Long, xRow As Long, iCol As Long
    Dim inDate As Date, outDate As Date
    
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
    
    With Sheet1
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
        
    
        Select Case .Range("C37").Value
            Case Is = "Available" 'OK to place booking
                With Sheet5 'Booking Status
                    Dim lCell As Range
                    Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                    lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                    lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                    lCell.Offset(0, -2).Value = inDate 'Check In Date
                    lCell.Offset(0, -1).Value = outDate 'Check Out Date
                    lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                    lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                    lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
                
                End With
            
            'show the room as booked on the Dates Tab
                
                'If we know the data is going to be there, can just look for the specific rows/columns
                iRow = Range("Dates").Find(inDate).Row
                xRow = Range("Dates").Find(outDate).Row
                iCol = Range("Function_Halls").Find(Sheet1.[C31]).Column
                
                'Assumes that booking_data is on sheet3
                With Sheet3
                    'Then we mark the entire range of cells as being booked
                    .Range(.Cells(iRow, iCol), .Cells(xRow, iCol)).Value = "Booked"
                End With
 
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
    End With
 
End Sub
 
@Luke M

Thanks for your code

i got one error Msg when i run the code that is
Object Variable or With Block Variable not set

error line is

Code: 
iRow = Range("Dates").Find(inDate).Row

Thanking you

With Regards

SP
 
Hmm. Try changing the 3 lines to this:
Code: 
               iRow = Range("Dates").Find(inDate, , xlFormulas).Row
                xRow = Range("Dates").Find(outDate, , xlFormulas).Row
                iCol = Range("Function_Halls").Find(Sheet1.[C31].Value, , xlFormulas).Column
I'm assuming that the dates are hard coded and not generated by a formula.
 
@Luke M

Sorry for my late replay,

as per you new code i change that one but again i get the error code for your reference i attach the file


Thanking you

With Regards

SP
 

Attachments

Hi SP,

Ok, I think I got it this time. THe dates on the "Dates" worksheet are generated by a formula, so I need to look at the value. But, I was saving the variable as a date before, which doesn't quite have the same "appearance". So, change the variables to be strings, and it appears to run now.
Code: 
Sub PostBooking()
 
    Dim msg As String, Ans As Variant
    Dim iRow As Long, xRow As Long, iCol As Long
    Dim inDate As String, outDate As String
    
   
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
   
    With Sheet1
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
           
        Select Case .Range("C37").Value
            Case Is = "Available" 'OK to place booking
               With Sheet5 'Booking Status
                   Dim lCell As Range
                    Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                    lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                   lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                   lCell.Offset(0, -2).Value = inDate 'Check In Date
                   lCell.Offset(0, -1).Value = outDate 'Check Out Date
                   lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                   lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                   lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
               
                End With
           
            'show the room as booked on the Dates Tab
               
                'If we know the data is going to be there, can just look for the specific rows/columns
               iRow = Range("Dates").Find(inDate, , xlValues).Row
                xRow = Range("Dates").Find(outDate, , xlValues).Row
                iCol = Range("Function_Halls").Find(Sheet1.[C31].Value, , xlValues).Column
               
                'Assumes that booking_data is on sheet3
               With Sheet3
                    'Then we mark the entire range of cells as being booked
                   .Range(.Cells(iRow, iCol), .Cells(xRow, iCol)).Value = "Booked"
                End With
 
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
   End With
 
End Sub
 
@LukeM

Thanks for your Sample File,

sir i have small doubt that is

sir i can't understand why the problem is starting from my side when the file is working in your system then why it's not working in mine, off course i am using Windows 8 - Office 2007. is there any option to set in my Reference Tools in VBA or what (My Id = sgmpatnaikATgmaildotcom)

Thanks

SP
 
Hi SP,

I'm running the same version of Office, so I don't think that is it. I did find one error in my code, but it was at a later line. I had mistakenly assumed that the range "Function_Halls" was on the Dates worksheet, but that is incorrect. I've added some debug lines to the code...if you could run it and let me know what appears in the Immediate window, that may be helpful.
Code: 
Sub PostBooking()
 
    Dim msg As String, Ans As Variant
    Dim iRow As Long, xRow As Long, iCol As Long
    Dim inDate As String, outDate As String
    
   
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
   
    With Sheet1
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
           
        Select Case .Range("C37").Value
            Case Is = "Available" 'OK to place booking
              With Sheet5 'Booking Status
                  Dim lCell As Range
                    Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                    lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                  lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                  lCell.Offset(0, -2).Value = inDate 'Check In Date
                  lCell.Offset(0, -1).Value = outDate 'Check Out Date
                  lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                  lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                  lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
              
                End With
           
            'show the room as booked on the Dates Tab
              
                'If we know the data is going to be there, can just look for the specific rows/columns
              
              'Debug information
              Debug.Print "indate: " & inDate
              Debug.Print "outDate: " & outDate
              Debug.Print "Date range: " & Range("Dates").Address
              
              iRow = Range("Dates").Find(inDate, , xlValues, xlWhole).Row
                xRow = Range("Dates").Find(outDate, , xlValues, xlWhole).Row
                iCol = Worksheets("Dates").Range("7:7").Find(Sheet1.[C31].Value, , xlValues, xlWhole).Column
               
                'Assumes that booking_data is on sheet3
              With Sheet3
                    'Then we mark the entire range of cells as being booked
                  .Range(.Cells(iRow, iCol), .Cells(xRow, iCol)).Value = "Booked"
                End With
 
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
  End With
 
End Sub
 
Hello Sir again i got the same Object Variable or With block variable not set

in the immediate Window i got this
Date Range: $A$8:$A$740
indate:
outDate:
Date Range:$A$8:$A$740

Sir is it possible to chat with you

Thanks

SP
 
That's...odd. The inDate and outDate variables should at least have been set to something. The cell ranges look correct...why are those variables not being set? :confused:

I can try to add some more debugging lines...do you perhaps see anything odd if you step through the code line by line using F8?
Code: 
Sub PostBooking()
 
    Dim msg As String, Ans As Variant
    Dim iRow As Long, xRow As Long, iCol As Long
    Dim inDate As String, outDate As String
    
   
    msg = "This Selected Item is currently booked for the selected date."
    msg = msg & vbLf & vbLf & "Please select a different Item"
    msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
   
    With Sheet1
        inDate = .Range("C20").Value
        outDate = .Range("G20").Value
        Debug.Print "Initial set of variables"
        Debug.Print inDate
        Debug.Print outDate
           
        Select Case .Range("C37").Value
            Case Is = "Available" 'OK to place booking
              With Sheet5 'Booking Status
                  Dim lCell As Range
                    Set lCell = Sheet5.Range("B65536").End(xlUp).Offset(1, 3)
                    
                    Debug.Print "lCell address: " & lCell.Address
                    lCell.Value = Sheet1.Range("C23").Value 'Guest Name
                  lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
                  lCell.Offset(0, -2).Value = inDate 'Check In Date
                  lCell.Offset(0, -1).Value = outDate 'Check Out Date
                  lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
                  lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
                  lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
              
                End With
           
            'show the room as booked on the Dates Tab
              
                'If we know the data is going to be there, can just look for the specific rows/columns
              
              'Debug information
              Debug.Print "indate: " & inDate
              Debug.Print "outDate: " & outDate
              Debug.Print "Date range: " & Range("Dates").Address
              
              iRow = Range("Dates").Find(inDate, , xlValues, xlWhole).Row
                xRow = Range("Dates").Find(outDate, , xlValues, xlWhole).Row
                iCol = Worksheets("Dates").Range("7:7").Find(Sheet1.[C31].Value, , xlValues, xlWhole).Column
               
                'Assumes that booking_data is on sheet3
              With Sheet3
                    'Then we mark the entire range of cells as being booked
                  .Range(.Cells(iRow, iCol), .Cells(xRow, iCol)).Value = "Booked"
                End With
 
            Case Else
                Ans = MsgBox(msg, vbInformation)
        End Select
        'Call PostRoomBooking
  End With
 
End Sub
 
@LukeM

Sorry to give you pain

As per you instruction i go through the line by line with pressing the F8 button without any error msg it's pass the line when i reach the line
Code: 
 iRow = Range([COLOR=#800000]"Dates"[/COLOR]).Find(inDate, , xlValues, xlWhole).Row
when i press the F8 again then i got the error msg for Object variable i think the error code is
Code: 
 xRow = Range([COLOR=#800000]"Dates"[/COLOR]).Find(outDate, , xlValues, xlWhole).Row

kindly guide me on this mistake because i want to learn for this mistake . I know if we can change the entire code in different mode then it will work but i want to learn from this code what is the mistake

Thanking you

With Regards

SP
 
And there were still no values being printed out for the inDate and outDate? :confused:
If the values are not being set, then the Find method is failing because we're not giving XL anything to look for. However, the variables should only not be being set if the cell we're are pointing to is blank or something. But, that doesn't appear to be the case. Can you double-check and make sure the variables are getting populated, aka, something is printing out from the Debug commands?
 
Hmm. Okay, more debugging...let me know if any odd messages appear when you try to book with this workbook, especially interested about values for inDate and outDate.
 

Attachments

Hello Sir,

Thanks for your New Code

I Check with your new Sample Workbook, when i press the PostBooking Button then i got some Msgs they are

1. We are now working with Worksheet:Gate Way

2. inDate has a value of: given date (11/7/2013)

3. outDate has a value of: given Date (14/07/2013)

4. Now we work with worksheet:Booking Status

5. Icell Address: $E$6

6. Dates Range Address: $A$8:$A$740

next coming error Msg for Object Variable With block Variable not set

Regards

SP
 
Hmm. I wonder if perhaps something is getting messed up based on regional settings for dates? I use mm-dd-yy, but I can see that you use dd-mm-yy. Rather than mess with that, let's try a different tactic. You should be able to ignore all the message boxes.
Code: 
Sub PostBooking()
 
Dim msg As String, Ans As Variant
Dim iRow As Long, xRow As Long, iCol As Long
Dim inDate As Date, outDate As Date
 
 
msg = "This Selected Item is currently booked for the selected date."
msg = msg & vbLf & vbLf & "Please select a different Item"
msg = msg & vbLf & vbLf & vbLf & "This booking will not be posted"
 
With Sheet1
    MsgBox "We are now working with worksheet:" & .Name
    inDate = .Range("C20").Value
    outDate = .Range("G20").Value
    MsgBox "inDate has a value of: " & inDate
    MsgBox "outDate has a value of: " & outDate
End With
 
Select Case Sheet1.Range("C37").Value
    Case Is = "Available" 'OK to place booking
        With Sheet5 'Booking Status
            MsgBox "Now we work with worksheet: " & .Name
            Dim lCell As Range
            Set lCell = .Range("B65536").End(xlUp).Offset(1, 3)
            MsgBox "lCell address: " & lCell.Address
            
            lCell.Value = Sheet1.Range("C23").Value 'Guest Name
            lCell.Offset(0, -3).Value = Sheet1.Range("C18").Value 'Enquiry Date
            lCell.Offset(0, -2).Value = inDate 'Check In Date
            lCell.Offset(0, -1).Value = outDate 'Check Out Date
            lCell.Offset(0, 1).Value = Sheet1.Range("C25").Value 'Address
            lCell.Offset(0, 2).Value = Sheet1.Range("C31").Value 'Hall Type
            lCell.Offset(0, 3).Value = Sheet1.Range("G31").Value ' Hall Amount
        
        End With
        
        'show the room as booked on the Dates Tab
        
        'If we know the data is going to be there, can just look for the specific rows/columns
        
        With Worksheets("Dates")
            MsgBox "Dates Range address: " & .Range("Dates").Address
            i = inDate - .Range("B5").Value
            x = outDate - .Range("B5").Value
            iRow = 8 + i '.Range("Dates").Find(inDate, , xlValues, xlWhole).Row
            xRow = 8 + x '.Range("Dates").Find(outDate, , xlValues, xlWhole).Row
            iCol = .Range("7:7").Find(Sheet1.[C31].Value, , xlValues, xlWhole).Column
        End With
        
        'Assumes that booking_data is on sheet3
        With Sheet3
            'Then we mark the entire range of cells as being booked
            .Range(.Cells(iRow, iCol), .Cells(xRow, iCol)).Value = "Booked"
        End With
    
    Case Else
    Ans = MsgBox(msg, vbInformation)
End Select
'Call PostRoomBooking
 
 
End Sub
 
You will always have risk when you try to get the property of the object(when the object is set to nothing) using following method:
Code: 
iRow = Range("Dates").Find(inDate, , xlValues, xlWhole).Row
Happens only when inDate is not in the "Dates" range.

So workaround would be two way:
Code: 
Set r = Range("Dates").Find(inDate, , xlValues, xlWhole)
If not r is nothing then iRow = r.row

Or
Code: 
On Error Resume Next
iRow = Range("Dates").Find(inDate, , xlValues, xlWhole).Row
On Error goto 0
 
Hi Shrivallabha,
That's what I thought at first, but the dates that are getting plugged in should be in the range. See post #19 I'm truly curious as to why the code is not working for him, when it works find on my machine. :(
 
If patnaik can manually check this [applies specifically to the case when error comes]:
5. Icell Address: $E$6
6. Dates Range Address: $A$8:$A$740
Click to expand...
Date in E6 should be there in A8:A740. If not, then Run Time Error 91 will come.
 
Hi Luke ,

I opened the file and clicked on the Book Posting button , after entering appropriate In and Out dates , and everything worked fine.

My date format is also mm/dd/yyyy , so probably this has something to do with it.

Narayan
 
@sgmpatnaik
Looks like we are running out of ideas...everything seems to work ok on our machines. Did the latest code modification work for you?
 
You must log in or register to reply here.
Back
Top