• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Daily review Mechanism - Follow up Sheet

KAMLESHDALVI

New Member
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
 

KAMLESHDALVI

New Member
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
TAGREVIEW DATEREVIEW 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
TAGREVIEW STATUS
AA1Review 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
 

Attachments

p45cal

Well-Known Member
try:
Code:
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

Well-Known Member
Oops, forgot the comment/note (and a few other things):
Code:
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
 
Last edited:

KAMLESHDALVI

New Member
Thanks Buddy :):DD 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 !!!
 

Attachments

p45cal

Well-Known Member
Can the code prevent same comment getting inserted in the code is Run twice
Yes:
Code:
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.
 
Top