A rather complicated Macro needed.

So I highlight a given column. I need a macro which first generates a
new column to the right of the single one highlighted. Then I need it
to scan all of the entries in the selected area and whenever it finds
an entry that has an underscore '_' it moves everything right of that
underscore to the new column (same row) just generated. Some entries
have more then one underscore and I only want the macro to move
everything right of the first underscore. When, it goes through the
column again and only checks entries which nothing has been moved. It
moves everything right of the fourth character in those entries which
do not have an underscore or have "NA" in the entry field.

I know this is a tough one, so thank you in advance. I don't even know
if it's possible to do.

~Matt

first highlight your column then link a button to this code (This alleviates
some considered problems with selection_change in my mind anyway)

Sub highlightandmove()
On Error Resume Next
Dim ran As Range
Columns(Selection.Row + 2).Insert shift:=xlShiftToRight
For Each ran In Selection.Cells
If InStr(1, ran, "_") > 0 Then
ran.Offset(0, 1) = Right(ran, Len(ran) - InStr(1, ran, "_"))
Else
ran.Offset(0, 1) = Right(ran, Len(ran) - 4)
End If
Next
End Sub

now this does not remove the text that is moved from the original cell i
don't know if you wanted that

--
When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?


"Matt" wrote:

> So I highlight a given column. I need a macro which first generates a
> new column to the right of the single one highlighted. Then I need it
> to scan all of the entries in the selected area and whenever it finds
> an entry that has an underscore '_' it moves everything right of that
> underscore to the new column (same row) just generated. Some entries
> have more then one underscore and I only want the macro to move
> everything right of the first underscore. When, it goes through the
> column again and only checks entries which nothing has been moved. It
> moves everything right of the fourth character in those entries which
> do not have an underscore or have "NA" in the entry field.
>
> I know this is a tough one, so thank you in advance. I don't even know
> if it's possible to do.
>
> ~Matt
>
>

Matt,

This isn't really that complicated. In fact, you could probably write a
formula to do just about everything you want except to insert a column.

You'd get a long way down the road by just recording the activity of
inserting a column to the left of the current column.

The formula to grab the stuff in front of the underscore is (assuming you're
in column E, but that is easy to make generic.
=IF(ISERROR(FIND("_",E4)),"",LEFT(E4,FIND("_",E4)-1))

I didn't follow all your requirements, but the nice thing about macros is
that by grabbing a book (Try "Excel 2003 Power Programming" by Walkenbach --
it's my favorite. I've been recommending John's work for years) you don't
have to translate requirements to a programmer. You can do it yourself.

Good luck.

"Matt" wrote:

> So I highlight a given column. I need a macro which first generates a
> new column to the right of the single one highlighted. Then I need it
> to scan all of the entries in the selected area and whenever it finds
> an entry that has an underscore '_' it moves everything right of that
> underscore to the new column (same row) just generated. Some entries
> have more then one underscore and I only want the macro to move
> everything right of the first underscore. When, it goes through the
> column again and only checks entries which nothing has been moved. It
> moves everything right of the fourth character in those entries which
> do not have an underscore or have "NA" in the entry field.
>
> I know this is a tough one, so thank you in advance. I don't even know
> if it's possible to do.
>
> ~Matt
>
>