Consulting

Results 1 to 12 of 12

Thread: VBA Code to edit and update existing data

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location

    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.
    Attached Files Attached Files

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I can't see any code added since this post:

    http://www.vbaexpress.com/forum/show...ht=#post396298
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    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.

  4. #4
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    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


    Booking_2.xlsm
    Last edited by Marsau; 12-14-2019 at 07:59 AM.

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    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

    Booking3.xlsm

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    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
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    Thank you once again.
    You are the champ in excel

  8. #8
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    Code changed and not giving any errors, but not updating data on booking chart or data sheet.

  9. #9
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    I have finally sorted it out.
    Maybe not the most eloquent code but working.
    Thanks once again for assistance paulked.



    Attached Files Attached Files

  10. #10
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    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

  11. #11
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Semper in excretia sumus; solum profundum variat.

  12. #12
    VBAX Regular
    Joined
    Sep 2019
    Posts
    57
    Location
    Is there away to type the arrival date an departure date and the rooms available for that time shows?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •