Daily review Mechanism - Follow up Sheet

[KAMLESHDALVI]

Hi

We have a daily review on shortages list with the Materials team and there are commitments on the forecasted receipt dates. There are two sheets in my File shortage list & Master Follow Up sheet.

The Field Tag is unique in both the sheets , I am trying to build the VBA code , Once the days review is completed and code runs I am expecting VBA code to perform as below

1. User will clear the data in columns J , K
2. User will review the multiple line items , will Update the columns J with the Review Date ( date field ) & columns K with the Remarks
3. The code should match the Tag no in the Master Follow Up sheet and insert the Review status as Split line in the Cell. If earlier data is available this should be inserted as separate line
4. The full Remark will be then stored as Note for the respective tag
5. Say the Review happens on 1st Dec 2020 , 25th Dec 2020 & 10th Jan for 150 Items and review status is updated. I want to utilize the Data for my subsequent reviews for How many times the commitments are shifting and what issues were discussed in last review

Hope I have not confused , looking for a Help

 

[pecoflyer]

Please post a sample sheet with some manually calculated results. Thanks

 

[KAMLESHDALVI]

Hi ,

Pl find attached I am not looking for any calculations as such

The first Sheet the Review sheet and my team conducts a review with the Material team for the shortages , the Line items wise issues and corrective actions are discussed and agreed and captured in the below table , Review status

TAG

REVIEW DATE

REVIEW STATUS

The second sheet will be the Master sheet , I am looking for the code when it is RUN the review status from first sheet will be inserted as separate line in the cell for the Matching TAG. The complete Data will be inserted as a Note for the respective tag

TAG	REVIEW STATUS
AA1	Review 15/12/2020-Forecasted receipt Date 10/01/2021 ; Review 10/1/2021-Forecasted receipt Date 25/01/2021 ; Review 15/1/2021-Forecasted receipt Date 5/02/2021

Based on the daily review the Data will get populated in the Master sheet which we can utilize for the deliberation on failed commitments / Correction required

Currently the reviews are based on the Notes or Memory and there is a limitation

 

[p45cal]

try:

Sub blah()
Set rngToProcess = Sheets("shortage list").Range("A1").CurrentRegion.Resize(, 3)
For Each rw In Intersect(rngToProcess, rngToProcess.Offset(1)).Rows
  Set Destn = Nothing
  Set Destn = Sheets("Master Follow Up sheet").Range("A:A").Find(rw.Cells(1).Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  If Not Destn Is Nothing Then
    myStr = Application.Trim(rw.Cells(2).Text) & "-" & Application.Trim(rw.Cells(3).Value)
    If Len(myStr) > 1 Then Destn.Offset(, 1).Value = Destn.Offset(, 1).Value & " ;" & vbLf & "Review " & myStr
  End If
Next rw
End Sub

 

[p45cal]

Oops, forgot the comment/note (and a few other things):

Sub blah()
Set rngToProcess = Sheets("shortage list").Range("A1").CurrentRegion.Resize(, 3)
For Each rw In Intersect(rngToProcess, rngToProcess.Offset(1)).Rows
  Set destn = Sheets("Master Follow Up sheet").Range("A:A").Find(rw.Cells(1).Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  If Not destn Is Nothing Then
    myStr = Application.Trim(rw.Cells(2).Text) & "-" & Application.Trim(rw.Cells(3).Value)
    If Len(myStr) > 1 Then
      destn.Offset(, 1).Value = IIf(Len(destn.Offset(, 1).Value) > 0, destn.Offset(, 1).Value & " ;" & vbCrLf, "") & "Review " & myStr
      If destn.Comment Is Nothing Then
        destn.AddComment destn.Offset(, 1).Text
      Else
        destn.Comment.Text Text:=destn.Offset(, 1).Text
      End If
    End If
  End If
Next rw
End Sub

 

[KAMLESHDALVI]

Thanks Buddy This has saved a lot of manual entries , sorry to push you but need to have a look on below as well

I have added the Departments , can the Code Update the entries in respective departments and the Department wise Block in the column B review status

Can the comments be indexed on the Department + Review date
Can the code prevent same comment getting inserted in the code is Run twice

Cheers !!!

 

[KAMLESHDALVI]

Hi

To add on

when the cursor will move on the Tag , the full Note ( Auto fit version ) will be visible

Thanks for the Help

 

[p45cal]

Can the code prevent same comment getting inserted in the code is Run twice

Yes:

Sub blah()
Set rngToProcess = Sheets("shortage list").Range("A1").CurrentRegion.Resize(, 3)
For Each rw In Intersect(rngToProcess, rngToProcess.Offset(1)).Rows
  Set destn = Sheets("Master Follow Up sheet").Range("A:A").Find(rw.Cells(1).Value, LookIn:=xlFormulas, lookat:=xlWhole, searchformat:=False)
  If Not destn Is Nothing Then
    myStr = Application.Trim(rw.Cells(2).Text) & "-" & Application.Trim(rw.Cells(3).Value)
    If Len(myStr) > 1 Then
      If InStr(destn.Offset(, 1).Value, myStr) = 0 Then
        destn.Offset(, 1).Value = IIf(Len(destn.Offset(, 1).Value) > 0, destn.Offset(, 1).Value & " ;" & vbCrLf, "") & "Review " & myStr
        If destn.Comment Is Nothing Then
          destn.AddComment destn.Offset(, 1).Text
        Else
          destn.Comment.Text Text:=destn.Offset(, 1).Text
        End If
      End If
    End If
  End If
Next rw
End Sub

I have added the Departments , can the Code Update the entries in respective departments and the Department wise Block in the column B review status

Can the comments be indexed on the Department + Review date

I'm sure that can be done but I won't do it; this forum is to help people with their code and formulae and the like, it is not a free code-writing service.
Your first post reads like a job specification.

 

[KAMLESHDALVI]

Understand and appreciate the support