+ Reply to Thread
Results 1 to 4 of 4

Konichiwa, VBA to Edit Current Data with ID number assigned.

Hybrid View

  1. #1
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2016, 2019, 365
    Posts
    198

    Konichiwa, VBA to Edit Current Data with ID number assigned.

    Hello,

    I'm creating a Leave and Liberty Tracker using a Template and code provided by Trevor Easton from http://www.onlinepclearning.com/. The designer places all of the Templates and code on his site for free to use. The problem I'm having is he didn't provide the code to "Edit Existing" Macro for a Booking or in my case a Leave or Liberty request. I've been able to change and add to the code from the original http://www.onlinepclearning.com/exce...ooking-system/.

    I attempted to use the AddMe macro as a starting point to accomplish the task but am unable to make to correct changes to update a selected item.

    Thank you in advance for any assistance.

    Jim
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Konichiwa, VBA to Edit Current Data with ID number assigned.

    You should have used the DeleteMe macro as a starting point. It finds the row in the database that matches the ID. Then instead of deleting the row, you update the values.

    Sub Editme()
    'declare the variables
        Dim ID As Range, c As Range, orange As Range
        Dim lastrow   As Long
        'set the object variable
        Set ID = Sheet2.Range("F3")
        'stop screen flicker
        Application.ScreenUpdating = False
        lastrow = Sheet4.Range("B" & Rows.Count).End(xlUp).Row
        Set orange = Sheet4.Range("B9:B" & lastrow)
        'find the value in the range
        For Each c In orange
            If c.Value = ID.Value Then
                'edit the row
                c.Offset(0, 0).Value = ID.Value
                c.Offset(0, 1).Value = Sheet2.Range("V3").Value
                c.Offset(0, 2).Value = Sheet2.Range("V4").Value
                c.Offset(0, 3).Value = Sheet2.Range("V5").Value
                c.Offset(0, 4).Value = Sheet2.Range("V6").Value
                c.Offset(0, 5).Value = Sheet2.Range("V7").Value
                'c.Offset(0, 6).Value =Sheet2.Range("AE3").Value
                'c.Offset(0, 7).Value =Sheet2.Range("AE4").Value
                'c.Offset(0, 8).Value =Sheet2.Range("AE5").Value
                'c.Offset(0, 9).Value =Sheet2.Range("AE7").Value
                c.Offset(0, 10).Value = Sheet2.Range("AM3").Value
                c.Offset(0, 11).Value = Sheet2.Range("AM4").Value
                c.Offset(0, 12).Value = Sheet2.Range("AM5").Value
                c.Offset(0, 13).Value = Sheet2.Range("AM6").Value
                c.Offset(0, 14).Value = Sheet2.Range("AM7").Value
                'c.Offset(0, 15).Value =Sheet2.Range("AZ3").Value
                'c.Offset(0, 16).Value =Sheet2.Range("BD3").Value
                'c.Offset(0, 17).Value =Sheet2.Range("AZ4").Value
                'c.Offset(0, 18).Value =Sheet2.Range("BD4").Value
                'c.Offset(0, 19).Value =Sheet2.Range("AZ5").Value
                'c.Offset(0, 20).Value =Sheet2.Range("BD5").Value
                'c.Offset(0, 21).Value =Sheet2.Range("AZ6").Value
                'c.Offset(0, 22).Value =Sheet2.Range("BD6").Value
                'c.Offset(0, 23).Value =Sheet2.Range("AZ7").Value
                'c.Offset(0, 24).Value =Sheet2.Range("BD7").Value
                Exit For
            End If
        Next c
        'et go home
        Sheet2.Select
        'update the calendar
        Bookings
    End Sub
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Forum Contributor sorensjp's Avatar
    Join Date
    12-23-2015
    Location
    Yokosuka, JP
    MS-Off Ver
    2016, 2019, 365
    Posts
    198

    Re: Konichiwa, VBA to Edit Current Data with ID number assigned.

    AlphaFrog--

    Thank you that did the trick. I had bee playing with that for a couple days and got nowhere. You hit it right on. Thank you again.

    Jim

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,653

    Re: Konichiwa, VBA to Edit Current Data with ID number assigned.

    You're welcome. Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Konichiwa
    By sorensjp in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 05-21-2016, 06:40 PM
  2. Replies: 1
    Last Post: 08-12-2015, 01:22 AM
  3. Konichiwa Everyone!
    By GasPath in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-20-2012, 04:42 AM
  4. Creating Excel VBA Form wanting to edit current data
    By snrh1228 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-09-2012, 01:02 PM
  5. Reload & edit data by unique ID number into Userform
    By BJ5352 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-06-2010, 10:25 PM
  6. Edit to current macro help
    By Stuwil in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2009, 06:49 PM
  7. Replies: 3
    Last Post: 06-10-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1