VBA Code to edit and update existing data

Printable View

12-13-2019, 09:08 AM
Marsau

1 Attachment(s)

VBA Code to edit and update existing data

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.
12-14-2019, 01:46 AM
paulked

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

http://www.vbaexpress.com/forum/show...ht=#post396298
12-14-2019, 06:55 AM
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.
12-14-2019, 07:25 AM
Marsau

1 Attachment(s)

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

Attachment 25634
12-18-2019, 11:21 AM
Marsau

1 Attachment(s)

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

Attachment 25656
12-18-2019, 12:12 PM
paulked

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

So

Code:

'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

Code:

'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
12-18-2019, 12:44 PM
Marsau

Thank you once again.
You are the champ in excel
12-18-2019, 12:45 PM
Marsau

Code changed and not giving any errors, but not updating data on booking chart or data sheet.:(
12-19-2019, 12:01 PM
Marsau

1 Attachment(s)

I have finally sorted it out. :clap:
Maybe not the most eloquent code but working.
Thanks once again for assistance paulked.
12-19-2019, 12:03 PM
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
12-19-2019, 12:54 PM
paulked

:thumb
12-19-2019, 01:31 PM
Marsau

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