Append from one cell to another cell

[Annpan79]

Hello,


I am trying to Create a logging sheet with the following columns. The sheet contains items which need to be tracked and each one has a 'log' against it which logs all previous actions. There is a meeting each week and before that meeting there is a manual task where We have to append the contents of 'Previous actions (J)' into 'Log(I)' and then replace 'Previous actions (J) with 'Next Actions' (K). This is a rather tedious and repetitive task but I am struggling with automating it in excel.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]LOG (I)[/TD]
[TD]PREVIOUS ACTIONS(J)[/TD]
[TD]NEXT ACTIONS(K)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 345"]
<tbody>[TR]
[TD="class: xl10118"]01/01/18 - No update
2/01/18 - chase update
3/01/18 - No update at present.
9/02 - No Update
26/02/2018 - No update
09/04 - No Update[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl10118"]
04/06 - OLD UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 357"]
<tbody>[TR]
[TD="class: xl10118"]14/06 - NEW UPDATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

In the example above, I would need first "04/06 - OLD UPDATE" to go into the bottom of the "LOG" column as a new line under 09/04 ****, then I would need
"14/06 - NEW UPDATE" to replace the contents of the "PREVIOUS ACTIONS " column.

I'm totally stuck and don't even know where to begin. If anyone would be able to help at all, I would be extremely grateful.


Thank you very much in advance.

Regards

AnnPan

 

[Peter_SSs]

Try this in a copy of your workbook.

Sub Update_Log()
  Dim lr As Long
  
  lr = Columns("I:K").Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  Application.ScreenUpdating = False
  With Range("I2:I" & lr)
    .Value = Evaluate(Replace(Replace("if(#="""","""",#)&if(%="""","""",char(10)&%)", "#", .Address), "%", .Offset(, 1).Address))
    .Offset(, 1).Value = .Offset(, 2).Value
    .Offset(, 2).ClearContents
  End With
  Application.ScreenUpdating = True
End Sub

 

[Annpan79]

Hi Peter,

Thank you for replying, I am so very sorry for the delay in responding - i have been ill and unable to test this out properly.

I have tested the code out and it is moving the text from column K to J perfectly, however it seems to be clearing the text from column I and replacing it with [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] in the majority of the cells. There doesn't seem to be any sort of pattern to why. Some of the cells have only one or two lines of data in them and it copies across fine, others have several and it copies OK - others that are the same it is replacing with [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=value]#value [/URL] ?

I hope that you are able to help. Again I am sorry for the late reply.

Kind regards

Ann

 

[Peter_SSs]

Hope you have fully recovered.

I suspect the issue might be with rows that have longish text. In any case, give this a try instead.

Sub Update_Log_v2()
  Dim lr As Long, i As Long
  Dim a As Variant
  
  lr = Columns("I:K").Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
  With Range("I2:K" & lr)
    a = .Value
    For i = 1 To UBound(a)
      If Len(a(i, 2)) > 0 Then a(i, 1) = a(i, 1) & Chr(10) & a(i, 2)
      a(i, 2) = a(i, 3)
      a(i, 3) = vbNullString
    Next i
    .Value = a
  End With
End Sub

 

[Annpan79]

Hi Peter,

I am on the mend now thank you

That works perfectly - thank you every so much

Kind regards

Ann

 

[Peter_SSs]

I am on the mend now thank you

That works perfectly - thank you every so much

Both things good to hear!