SUMPRODUCT in VBA for booking system

[afoufou]

Hi,

I'm looking for help on my VBA code.

I'm making a booking system using excel, users select a room, date and time and send the request with a button.
I've made the button return the informations in a seperate sheet that looks like that :

Room	Time	Date
3110	08:10	02.12.21
3112	08:10	02.12.21
3110	09:20	02.12.21

Now, without VBA I've used the following formula to check for availability and display it in a calendar :

=1-SUMPRODUCT((return!$B$2:$B$1000=Reservation!$D$5)*(return!$H$2:$H$1000=Reservation!$F11)*(return!$G$2:$G$1000=Reservation!G$10))

It returns a value = 1 if available, = 0 if already booked once and < 0 if booked more than once.

Now I'd like to use the same formula in VBA to prevent double booking. I've got the IF part covered but I can't translate the formula above in VBA.

If you have any idea how to apply the formula in VBA that'd be great.

 

[offthelip]

try this:

Sub test()
'=1-SUMPRODUCT((return!$B$2:$B$1000=Reservation!$D$5)*(return!$H$2:$H$1000=Reservation!$F11)*(return!$G$2:$G$1000=Reservation!G$10))
With Worksheets("return")
inarr = .Range(.Cells(2, 1), .Cells(1000, 8))
End With
With Worksheets("Reservation")
'i have aassumed room is in D5, time in F11 and date in G10
room = .Range(.Cells(5, 4), .Cells(5, 4)) ' D5
tim = .Range(.Cells(11, 7), .Cells(11, 7)) ' F11
dat = .Range(.Cells(10, 8), .Cells(10, 8)) 'G10
End With
avail = 1
For i = 1 To UBound(inarr, 1)
  ' I have assumed the room is in column B , time in column F and Date in column G
  If inarr(i, 2) = room And inarr(i, 7) = tim And dat = inarr(i, 8) Then
   avail = avail -1
   
 End If
Next i
MsgBox ("Room available " & avail)

End Sub

 

[Marc L]

Hi, as the formula can be directly used under VBA :​

Sub Demo1()
    MsgBox [1-SUMPRODUCT((return!$B$2:$B$1000=Reservation!$D$5)*(return!$H$2:$H$1000=Reservation!$F11)*(return!$G$2:$G$1000=Reservation!G$10))]
End Sub

 

[afoufou]

Great !

Thanks Marc, I tried many syntaxe to reuse the same formula but you gave me the one that works !

offthelip : thanks as well, I'll save yours in case I need to go more in depth with VBA. Great work.

thanks again.