VBA Code to edit and update existing data

[Marsau]

I have a booking system.
I have learned such a lot from this project with help from members on this forum.
I have created a button to edit and update existing data.
Can anybody assist with code for this please.

Secondly the booking works by choosing an arrival date and amount of days for stay........then the departure date is calculated. I would like to rather choose the arrival and departure date, the days calculated than the other way around. assistance would be appreciated.

[paulked]

I can't see any code added since this post:

http://www.vbaexpress.com/forum/showthread.php?66176-Room-Booking-System&p=396298&highlight=#post396298

[Marsau]

Under my assorted module I had to add "Bookings" to the following:

Sub AddWk()
If Sheet2.Range("K7") = "" Then
Sheet2.Range("K7") = 7
Else
Sheet2.Range("K7") = Sheet2.Range("K7") + 7
End If
Bookings
End Sub


Sub MinusWk()
If Sheet2.Range("K7") = "" Then
Sheet2.Range("K7") = -7
Else
Sheet2.Range("K7") = Sheet2.Range("K7") - 7
End If
Bookings
End Sub


Now that its all working I will Start on creating the User forms this week.

[Marsau]

I have managed to sort out the arrival date and departure date myself.
"Edit existing booking" button still needs a macro to update changes made.
Would also like to see if I can populate only the second block of a date for arrival and the first block of the date on the departure date


25634

[Marsau]

Under assorted module i have added a sub edit me to update booking changes. Macro linked to "Edit existing Booking"
But not working
Please help

25656

[paulked]

You need to end With with End With and If - Then with End If

So


'find the next free row
Set nextrow = Fws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
With nextrow
If .Offset(0, -1).Value = ID.Value Then
.Value = Bws.Range("V3").Value
.Offset(0, 1).Value = Bws.Range("V4").Value
.Offset(0, 2).Value = Bws.Range("V5").Value
.Offset(0, 3).Value = Bws.Range("V6").Value
.Offset(0, 4).Value = Bws.Range("V7").Value
.Offset(0, 5).Value = Bws.Range("AE3").Value
.Offset(0, 6).Value = Bws.Range("AE4").Value
.Offset(0, 7).Value = Bws.Range("AE5").Value
.Offset(0, 8).Value = Bws.Range("AE7").Value
.Offset(0, 9).Value = Bws.Range("AN3").Value
.Offset(0, 10).Value = Bws.Range("AN4").Value
.Offset(0, 11).Value = Bws.Range("AN5").Value
.Offset(0, 12).Value = Bws.Range("AN6").Value
.Offset(0, 13).Value = Bws.Range("AN7").Value
.Offset(0, 14).Value = Bws.Range("AZ3").Value
.Offset(0, 15).Value = Bws.Range("BD3").Value
.Offset(0, 16).Value = Bws.Range("AZ4").Value
.Offset(0, 17).Value = Bws.Range("BD4").Value
.Offset(0, 18).Value = Bws.Range("AZ5").Value
.Offset(0, 19).Value = Bws.Range("BD5").Value
.Offset(0, 20).Value = Bws.Range("AZ6").Value
.Offset(0, 21).Value = Bws.Range("BD6").Value
.Offset(0, 22).Value = Bws.Range("AZ7").Value
.Offset(0, 23).Value = Bws.Range("BD7").Value
'run the filter to limit data


becomes


'find the next free row
Set nextrow = Fws.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
With nextrow
If .Offset(0, -1).Value = ID.Value Then
.Value = Bws.Range("V3").Value
.Offset(0, 1).Value = Bws.Range("V4").Value
.Offset(0, 2).Value = Bws.Range("V5").Value
.Offset(0, 3).Value = Bws.Range("V6").Value
.Offset(0, 4).Value = Bws.Range("V7").Value
.Offset(0, 5).Value = Bws.Range("AE3").Value
.Offset(0, 6).Value = Bws.Range("AE4").Value
.Offset(0, 7).Value = Bws.Range("AE5").Value
.Offset(0, 8).Value = Bws.Range("AE7").Value
.Offset(0, 9).Value = Bws.Range("AN3").Value
.Offset(0, 10).Value = Bws.Range("AN4").Value
.Offset(0, 11).Value = Bws.Range("AN5").Value
.Offset(0, 12).Value = Bws.Range("AN6").Value
.Offset(0, 13).Value = Bws.Range("AN7").Value
.Offset(0, 14).Value = Bws.Range("AZ3").Value
.Offset(0, 15).Value = Bws.Range("BD3").Value
.Offset(0, 16).Value = Bws.Range("AZ4").Value
.Offset(0, 17).Value = Bws.Range("BD4").Value
.Offset(0, 18).Value = Bws.Range("AZ5").Value
.Offset(0, 19).Value = Bws.Range("BD5").Value
.Offset(0, 20).Value = Bws.Range("AZ6").Value
.Offset(0, 21).Value = Bws.Range("BD6").Value
.Offset(0, 22).Value = Bws.Range("AZ7").Value
.Offset(0, 23).Value = Bws.Range("BD7").Value
End If
End With
'run the filter to limit data

[Marsau]

Thank you once again.
You are the champ in excel

[Marsau]

Code changed and not giving any errors, but not updating data on booking chart or data sheet.:(

[Marsau]

I have finally sorted it out. :clap:
Maybe not the most eloquent code but working.
Thanks once again for assistance paulked (http://www.vbaexpress.com/forum/member.php?4872-paulked).

[Marsau]

Here is the changes made:

Sub EditMe()
'declare the variables
Dim Bws As Worksheet, Fws As Worksheet, f As Range, wID As Variant
'turn off screen updating
Application.ScreenUpdating = False
'variables
Set Bws = Sheet2
Set Fws = Sheet4
'check for sufficent data
If Bws.Range("H3").Value = "" Or Bws.Range("V3").Value = "" Or _
Bws.Range("V4").Value = "" Or Bws.Range("AN3").Value = "" Then
MsgBox "There is insufficient data to Edit"
Exit Sub
End If
'find ID
wID = Bws.Range("H3").Value
Set f = Fws.Range("B9:B" & Fws.Range("B" & Rows.Count).End(xlUp).Row).Find(wID, , xlValues, xlWhole)
If f Is Nothing Then
MsgBox "ID does not exists"
Exit Sub
End If
With f
.Offset(0, 1).Value = Bws.Range("V3").Value
.Offset(0, 2).Value = Bws.Range("V4").Value
.Offset(0, 3).Value = Bws.Range("V5").Value
.Offset(0, 4).Value = Bws.Range("V6").Value
.Offset(0, 5).Value = Bws.Range("V7").Value
.Offset(0, 6).Value = Bws.Range("AE3").Value
.Offset(0, 7).Value = Bws.Range("AE4").Value
.Offset(0, 8).Value = Bws.Range("AE5").Value
.Offset(0, 9).Value = Bws.Range("AE7").Value
.Offset(0, 10).Value = Bws.Range("AN3").Value
.Offset(0, 11).Value = Bws.Range("AN4").Value
.Offset(0, 12).Value = Bws.Range("AN5").Value
.Offset(0, 13).Value = Bws.Range("AN6").Value
.Offset(0, 14).Value = Bws.Range("AN7").Value
.Offset(0, 15).Value = Bws.Range("AZ3").Value
.Offset(0, 16).Value = Bws.Range("BD3").Value
.Offset(0, 17).Value = Bws.Range("AZ4").Value
.Offset(0, 18).Value = Bws.Range("BD4").Value
.Offset(0, 19).Value = Bws.Range("AZ5").Value
.Offset(0, 20).Value = Bws.Range("BD5").Value
.Offset(0, 21).Value = Bws.Range("AZ6").Value
.Offset(0, 22).Value = Bws.Range("BD6").Value
.Offset(0, 23).Value = Bws.Range("AZ7").Value
.Offset(0, 24).Value = Bws.Range("BD7").Value
End With
FilterRng 'run the filter to limit data
Bws.Select 'select the bookings sheet
Bookings 'run the macro to add the bookings
Clearme 'clear the values
End Sub

[paulked]

:thumb

[Marsau]

Is there away to type the arrival date an departure date and the rooms available for that time shows?