Need some help adjusting some VBA code

Topic

New Reply

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….

Reply | Quote

Replies

Is the layout you posted unchanged from the original? (i.e. you haven’t inserted that col D without altering the code?)

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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……….??…??

Reply | Quote

Yes, it would.

Reply | Quote

:rolleyes::rolleyes:…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…

Reply | Quote

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.

Reply | Quote

Thank you very much for that…..I will try it and do some testing and let you know….ttyl…thanks, again

Reply | Quote

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.

Reply | Quote

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!)

Reply | Quote

Thank you, Rory…I will return to the othe rpost and pick things up from there….

Reply | Quote