Results 1 to 13 of 13
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Need some help adjusting some VBA code

    Hi all....I need some help adjusting some VBA code. The attached w/book is a sample (ie: month of January only). It works like this:

    Workdays are shown as morning (white) and afternoon (sand color).If I have something scheduled for morning of Jan 5/11 (eg:row 23) and I want to re-schedule it to the afternoon of Jan 7 (row 33 or 34), I go to Y23 and enter 33 (or 34)....this will move the data from row 23 to row 33 (or row 34). It also works if I want to move something forward in time (eg: from Jan 10 to Jan 7)....the re-scheduling is done by entering the 'new' row number in column Y.

    If you enter a row number where column C shows "closed" (eg: row 13-16 or row 75-78), you get a box saying that the row (aka 'date') is not a work day (eg: Jan 1, Jan 3, Jan 21).

    I would like to be able to use column D in place of column Y (easier to view etc, especially as columns J-X widen once they have data in them). There are 2 parts of code that make all of this happen --- you can view code on the Schedules sheet; it is the 2nd Private Sub that does part of the job. And, then in Module 2, you will see the code that does the rest of the job.

    I am fairly sure that the 2nd Private sub only needs to have ("y:y") changed to ("d:d") and the line "Call Postpone(Target, 22)" (22 columns from Y to C) should probably be "Call Postpone(Target,1) (being 1 column from D to C).

    I need help figuring out how to change the code in Module 2....as far as I can understand, the reference to -17 (in the line "If Cells(NewDateRow + i, iTargetCol - 17) = "" Then") is the # of columns (17) moving right to left from Y to H (the first column of data that is being moved). If I use col D instead of col Y, I imagine that the -17 should be something like 4 (being 4 columns from D to H). And, I think that the line "For iCol=1 to 17" refers to the 17 columns to the left of Col Y (col X-H, moving right to left). I imagine that also would change b/c col Y will become col D (and the the columns being moved would be H-X (4-22).

    I have made these changes but they're not enough to make it work (so I undid the changes and restored the original code)......I am not sure, for example, if references to -iCol are a minus sign (and maybe should be a plus sign because I am moving left-to-right (from col D to X) or are they simpy acting as hyphens etc etc.....is someone able to fully decipher this code and suggest changes to it that will allow me to use Col D, rather than col Y, to do the 're-scheduling' ? Thanks....
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Is the layout you posted unchanged from the original? (i.e. you haven't inserted that col D without altering the code?)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Rory.....the layout I posted is the current properly-working layout, if I can say it that way----in other words, everything is working as it should if I am using col Y to 'do the re-scheduling'.....if you open the file I attached and re-schedule one of the items shown by going to the end of a row of data (eg row 23) entering a new row number (eg: 31) in cell Y23, it will move the contents of row 23 to row 31 (first time slot on Jan 7th; if you were to enter 75, which is the first time slot on Jan 25, it will warn you that Jan 25 is not a work day).

    I am trying to re-write the code so instead of using col Y, I can use col D to do the re-schedule (b/c it is presently unused) as it would be more convenient to be able to use D --- I would hide col C, and then D would be between B and E, making it easier to see the row number that I want to 'reschedule to', if that explains things..?...so, I guess that means 'yes' to your question, if I understand what you're asking..

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi again, Rory...the problem I am having is that I understand the relationship b/w the Private Sub on the worksheet and the Public Sub in module 2, but I don't fully understand parts of the Public Sub (eg: what the Dim is all about, or the parts about Integers etc etc) and so I can't really figure out how the code currently works....I suppose if I could decipher how it currently works, I could figure out how to change it but I don't want to just start guessing at what has to be changed....make sense?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Try changing the Worksheet_Change code to:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
      If Target = EntryVal Or Target.Row < 12 Then Exit Sub
      EntryVal = Target
      If Not Intersect(Target, Range("D:D")) Is Nothing Then
        Call Postpone(Target, "C", "H", "X")
      End If
    End Sub
    and the PostPone sub to:
    Code:
    Public Sub Postpone(Target As Range, strStatusCol As String, strDataStartCol As String, strDataEndCol As String)
       Const MsgTitle = "Trying to re-schedule ?...sorry..."
       Dim NewDateRow        As Long
       Dim i                 As Long
       Dim iTargetCol        As Integer
       Dim lngRow            As Long
       Dim wks               As Excel.Worksheet
       Dim rngCell           As Excel.Range
    
    
       Set wks = Target.Worksheet
       lngRow = Target.Row
       iTargetCol = Target.Column
    
    
       Application.EnableEvents = False
    
    
       'Locate the first row of the requested new date
       With wks
          On Error GoTo BadDate
          NewDateRow = Application.WorksheetFunction.Match(Target.Value, .Range("b:b"), 0)
          On Error GoTo 0
    
    
          If .Cells(NewDateRow, strStatusCol) = "Closed" Then GoTo BadDate
    
    
          'Populate the "Rescheduled from" cell
          ' ## APPEARS TO BE POPULATING COL D, BUT THAT'S THE ONE WE CHANGED ANYWAY?! ##
          '      Target.Value = .Cells(Target.Row, iTargetCol - iOffset + 1)
    
    
          'Locate the first available row
          For i = 0 To 10
             If .Cells(NewDateRow + i, strDataStartCol) = "" Then
                Exit For
             End If
          Next i
          If i = 11 Then
             GoTo BadDate
          Else
             NewDateRow = NewDateRow + i
          End If
    
    
          'Move data to new row for columns 1-6 before target
          For Each rngCell In .Range(.Cells(NewDateRow, strDataStartCol), .Cells(NewDateRow, strDataEndCol)).Cells
             rngCell.Value = .Cells(lngRow, rngCell.Column).Value
             .Cells(lngRow, rngCell.Column).ClearContents
          Next rngCell
    
    
          Target.ClearContents
    
    
          .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, _
                   AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
       End With
       GoTo ExitHandler
    
    
    BadDate:
       On Error GoTo 0
       MsgBox "" & "The selected date is not a work day.", _
              vbCritical, MsgTitle
       Target = ""
    
    
    ExitHandler:
       Application.EnableEvents = True
    End Sub
    You'll also need to unlock the column D cells of course.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Rory.,...this looks very slick...still testing, and I decided to switch B and D, so I made the code changes and am testing....one question that I just thought of as I test it -- right now, I enter the 'new' row number to move data from one row to another....would if be possible to add something that would allow either a 'move' or a 'copy' -- eg: if I am on row 25 and I want to re-sched to row 31, I could enter 31M to move data from 25 to 31...or enter 31C to copy data from 25 to 31..........??...??

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes, it would.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    ...any chance that you could add that to your code....if I read your code, it basically 'pastes' values and clears the old row...?....and a change would be to copy values and not clear the old row, but I don't have a clue as to how to intersperse that into your existing code....alternating 'if' statements, perhaps, but I really don't know....thanks, in advance, for your patience...

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Essentially:
    Code:
    Public Sub Postpone(Target As Range, strStatusCol As String, strDataStartCol As String, strDataEndCol As String)
       Const MsgTitle = "Trying to re-schedule ?...sorry..."
       Dim NewDateRow        As Long
       Dim i                 As Long
       Dim iTargetCol        As Integer
       Dim lngRow            As Long
       Dim wks               As Excel.Worksheet
       Dim rngCell           As Excel.Range
    
    
       Set wks = Target.Worksheet
       lngRow = Target.Row
       iTargetCol = Target.Column
    
    
       Application.EnableEvents = False
    
    
       'Locate the first row of the requested new date
       With wks
          On Error GoTo BadDate
          NewDateRow = Application.WorksheetFunction.Match(Val(Target.Value), .Range("b:b"), 0)
          On Error GoTo 0
    
    
          If .Cells(NewDateRow, strStatusCol) = "Closed" Then GoTo BadDate
    
    
          'Populate the "Rescheduled from" cell
          ' ## APPEARS TO BE POPULATING COL D, BUT THAT'S THE ONE WE CHANGED ANYWAY?! ##
          '      Target.Value = .Cells(Target.Row, iTargetCol - iOffset + 1)
    
    
          'Locate the first available row
          For i = 0 To 10
             If .Cells(NewDateRow + i, strDataStartCol) = "" Then
                Exit For
             End If
          Next i
          If i = 11 Then
             GoTo BadDate
          Else
             NewDateRow = NewDateRow + i
          End If
    
    
          'Move data to new row for columns 1-6 before target
          For Each rngCell In .Range(.Cells(NewDateRow, strDataStartCol), .Cells(NewDateRow, strDataEndCol)).Cells
             rngCell.Value = .Cells(lngRow, rngCell.Column).Value
             If Right(UCase(Target.Value), 1) = "M" Then .Cells(lngRow, rngCell.Column).ClearContents
          Next rngCell
    
    
          Target.ClearContents
    
    
          .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, _
                   AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
       End With
       GoTo ExitHandler
    
    
    BadDate:
       On Error GoTo 0
       MsgBox "" & "The selected date is not a work day.", _
              vbCritical, MsgTitle
       Target = ""
    
    
    ExitHandler:
       Application.EnableEvents = True
    End Sub
    which will move the cell if you enter a number followed by M, and copy it otherwise.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you very much for that.....I will try it and do some testing and let you know....ttyl...thanks, again

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Rory...I have been working with this all evening and it looks great...thank you very much for your help....I need to 'study' the code some more to try to understand it (so that I can fix/adjust it in the need arises) and so I may come back to you with some questions about what certain pieces of the code mean, do....etc etc......and, I need to ask you another question about a problem that I am having with this same file -- not sure if I can veer off a bit within this thread. I started a thread called Range, INDEX lookup (?); selecting dates, time/rooms for project meetings and it is still active.....I have been working with Steve (sdckapr), one of the moderators in this forum....I (and maybe Steve) are going bonkers trying to figure out why some application.input code (??) that Steve provided works with XL 2010, but not with XL2003........it seems as if it is pretty basic stuff (not to me, but likely to others..) but it is maddening that it won't work on XL2003.....there is a file uploaded called Misfire.xls that will illustrate the problem....again, my apologies if it isn't proper to be discussing a thread in another thread, but both he and I are stumped......any suggestions as to how I/we can approach this? Steve seems like an extremely talented guy, and if he's stumped, there is little chance for the rest of us...)...thanks.

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No worries - I've posted a reply in the other thread to keep the two separate. (PS I'm not a moderator here these days!)
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Rory...I will return to the othe rpost and pick things up from there....

Posting Permissions

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