Aug 28 2020 03:31 PM
Aug 28 2020 05:51 PM - edited Aug 28 2020 05:54 PMSolution
Hello, I hope I can help you
Well, to be honest I didn't understand the main purpose of transfer all values from 1 cell to to another via code.
But I can presume you are looking for something that can take piece of piece and then do this transferring. I prepare the following loop so you can use in your workbook.
Remember that what this code is doing is breaking the names separated by comma and transferring one by one to another cell, just like you wrote. Please advise me if is something different from this.
Sub TranferValuesbyLoop() Dim ListOfNames() As String ListOfNames = Split(Worksheets("sheet1").Range("D4").Value, ", ") Dim item As Variant, Destination As Range Set Destination = Worksheets("sheet2").Range("E4") For Each item In ListOfNames If Len(Destination) > 0 Then Destination.Value = Destination.Value & "," & item Else Destination.Value = item End If Next item End Sub
Aug 28 2020 06:23 PM
Thank you for response works good, but my fault in explanation. I don't want to have sheet 2 look like sheet 1 in end. I want first name pasted then next pasted over that. Jim Smith pasted in then Ann waash over that. not jim smith, then ann waash added. jim smith pasted in cell I'll run macro associated with jim smith. then loop around paste in ann waash over jim smith and I'll run macro associated with ann waash. until last name is done. Also the paste has to be paste special.values. Thanks again for looking at this@Juliano-Petrukio
Aug 28 2020 09:03 PM
Looks like you want to invert the order of the names
Original: Jim Smith, Annn wash, Hammerton, Polhollow, Undermam
Result: Undermam, Polhollow, Hammerton, Annn wash, Jim Smith
Regarding Paste Values only you don't need to worry because the values are stored in an variable called ListOfNames. Only the values are stored there. As looks like you want to invert the order, the function ReverseArray does that and then each value of the reversed list is "paste" in the cell E4 of the sheet2.
Option Explicit Sub TranferValuesbyLoop() Dim ListOfNames As Variant ListOfNames = Split(Worksheets("sheet1").Range("D4").Value, ", ") ListOfNames = ReverseArray(ListOfNames) Dim item As Variant, Destination As Range Set Destination = Worksheets("sheet2").Range("E4") For Each item In ListOfNames If Len(Destination) > 0 Then Destination.Value = Destination.Value & ", " & item Else Destination.Value = item End If Next item End Sub Function ReverseArray(arr As Variant) As Variant Dim val As Variant With CreateObject("System.Collections.ArrayList") For Each val In arr .Add val Next val .Reverse ReverseArray = .Toarray End With End Function
Aug 28 2020 10:24 PM
I don't know where you got that from my explanation. Trying to think how I can explain. Put Jim smith in sheet 2 . that will trigger a macro I have for jim smith to run. jim smith macro runs. then put ann waaash into sheet2 (jim smith goes away). triggering a macro for ann waash to run. Then put hammerton in sheet2 (ann waash goes away). triggering a macro for hammerton to run. same until last name in string is done. I am putting code to run when the cell value of sheet 2 changes I do not want a range of names in sheet 2 like in sheet 1. I want first name brought over then the second name erasing the first name so on and so on@Juliano-Petrukio