VBA Cut partial content/paste function

[jweasl]

Ok I've got it mostly figured out (I thought), but Excel doesn't like something about the code. I'm using Office 2010. When it tries to run the second line of this code, I get a 438 error:

Code:

Selection.Characters(83, Len(Selection) - 82).Cut</pre>

I'm guessing that Cut isn't an allowed method - but I'm not having any luck finding a list of operations I can use anywhere...

 

[JLGWhiz]

Ok I've got it mostly figured out (I thought), but Excel doesn't like something about the code. I'm using Office 2010. When it tries to run the second line of this code, I get a 438 error:

Code:

Selection.Characters(83, Len(Selection) - 82).Cut
</PRE>

I'm guessing that Cut isn't an allowed method - but I'm not having any luck finding a list of operations I can use anywhere...

It is telling you that the Characters object does not support the Cut method. What you can do use the Left or Right functions to get your data moved to the destination cell, then use the Characters object to delete the unwanted portion of the text.

 

[jweasl]

I don't want to delete any text, I just want to move excess text to another cell. I'm trying to make it so the users don't have to manually move the content.

 

[JLGWhiz]

The idea is to use eithe Left() or Right() functions to capture the text you want to move from the source cell. Put that in the destination cell. Then delete that same text from the source cell using the Characters method to deleted partial strings.

Dim src As Range, dst As Range
Set src = Range("A1")
Set dst = Range("A2")
dst = Left(src.Value, 5)
src.Characters(1, 5).Delete

It is the same effect as cutting and pasting.

 

[jweasl]

Ok, looks like that's the right path. Can you point me to any instructions on how to get it to work? I'm changing values with no luck, and I found a really long article on working with Ranges that I'm reading through, no luck yet there either.

Thanks