A tricky date problem

[coraiw]

Hi there,

I have a rather complex problem and wonder if anyone can help. I have 3 columns A, B and C, with each row representing a different company.

Essentially I am trying to calculate the number of days between orders for different companies, by inputting the latest order date.

So, column A contains the original order date, column B contains the latest order date and column C contains the difference - which is the number of days.

What I am wondering is whether there is a way that I can get the date in column B (the latest order date) to shift over to column A when I receive a new order (which now needs to replace this date). Does anyone know of a way to do this? or if it is even possible.

Many thanks

 

[craig.penny]

Are you okay with using vba to accomplish this?

 

[craig.penny]

I hope the answer is yes because I think I've got it worked out. You need both of these procedures in the code module behind the sheet in question.

Private Sub Worksheet_Activate()
Dim x As Long
Dim FR As Long: FR = 1
Dim LR As Long: LR = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
Dim ComText As String
For x = FR To LR
      ComText = ""
      ComText = Cells(x, 2)
      With Cells(x, 2)
            If .Comment Is Nothing Then
                  .AddComment
            Else
                  .Comment.Text Text:=""
            End If
            .Comment.Text Text:=ComText
            .Comment.Shape.TextFrame.AutoSize = True
      End With
Next x
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Long
Dim y As Integer
Dim FR As Long: FR = 1
Dim LR As Long: LR = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
For x = FR To LR
      If Not Cells(x, 2).Comment Is Nothing Then
            If Cells(x, 2) <> Cells(x, 2).Comment.Text Then
                  Cells(x, 1) = Cells(x, 2).Comment.Text
                  With Cells(x, 2)
                        .Comment.Text Text:=""
                        .Comment.Text Text:=Cells(x, 2).Text
                  End With
            End If
      End If
Next x
 
End Sub

I'd bet there will be kinks to work out but I think the basic structure is good.

 

[coraiw]

Hi craig.penny,

Thanks for the reply. I will check it out and see if it works...