VBA: Searching text in cells in a specific column and then moving that text to a different column

Copper Contributor

Hello all,

I've been searching for the correct way to do this for a couple of days now.

I need to find cells that contain the characters "*, *" (the divider in a name entry)

Then I need to offset that 1 column and 1 row. I've been able to perform partial successful scripts but I just can't seem to get it right so it will actually continue checking for this case through the entire document. On the cases where I have gotten partial success, it ends up looking like crazy and moving things weird.

 

this was giving me some success but not what I needed.

If InStr(A1, "*, *", "*, *.", vbTextCompare) Then ActiveCell.Cut
ActiveCell.Offset(1, 1).Select
ActiveCell.Insert
ActiveCell.Offset(0, -1).Select

 

Granted I can do it via formula and all but I would prefer a Macro that I can just run on the sheets and move the data to align as needed and make things move faster.

 

Via formula I just offset this one row and run it and it pulls what I need onto the right row but it's not the solution  I would prefer as I need to do a lot of variations of this to get all the other parts of the text moved:

=IFERROR(IF(SEARCH("*, *",A1,1),A1),"")

I also have to do this for other rows that contain about 25 different total form names.

 

The data to be moved to columns follows a structure like this:

Last, First

ID#

Doc1

Doc2

Doc3

Last, First

ID#

Doc2

Doc3

Last, First

ID#

Doc1

Doc2

 

not all people have the same requirements on this list so the document IDs can vary, I'm trying to get everything to be on the same row as the ID#

 

If anyone can help direct me on this I would greatly appreciate it. I've had good success with VBA in the past but this round is really messing me up.

0 Replies