VBA to Cut and Paste Based on Character Count

[vba_student]

Hi All,

First time poster, but a huge fan of the forum and have received tremendous assistance from past posts in my work - I don't know where I would be without the help from this community.

I am in need of a simple VBA code to execute cut and paste based on the number of characters in a cell and repeat for each row.

The Data appears as follows:

P Q R
12345678 Calculate Account

More specifically, based on my requirements in the above data:
1. Count the number of characters on column P
2. If it's less than or equal to 8 characters THEN;
3. Cut and paste the contents in column P and Q over to the right one cell.
4. Repeat for each row

In the end, I'd like the Data to appear as follows:

P Q R
12345678 Calculate Account

Some caveats:
1. The data is in text format (not sure if this will have an impact).
2. The data is in a formatted table, which I already see won't take the function - LEN(text).

Many Thanks in advance!

 

[JLGWhiz]

hi vba_student, welcome to the forum. Excel considers a space as a character, does your table have strings separated by spaces in column P and will that affect your 8 character count? Also, is the table a named table as in a Tables collection, or just an organized area of the worksheet with borders? If it is an Excel table (part of collection) then what is the table name? It makes a difference in how the vba code is written to address the cell locations.

 

[vba_student]

Hi JLGWhiz,

Thanks for your response! To answer your question, there are no spaces in column P that would affect the character count - I doubled checked to ensure there are no space before or after the value. And regarding the table, it is a tables collection with name, "Table_ATV_QUERIES.accdb20".

My before and after data view did not come out as I intended in my initial post, so I'm going to try it in a different format.

Before:

Cell P2 = 12345678
Cell Q2 = Calculate Account
Cell R2 = Blank

After:

Cell P2 = Blank (since there are less than or equal to 8 characters - Cut and paste "12345678 to Q2)
Cell Q2 = 12345678 (Paste from P2)
Cell R2 = Calculate Account (Paste from Q2)

So, basically since Cell P2 has 8 characters, the contents in P2 and Q2 shift one cell to the right, leaving P2 blank. And I would like to repeat this for every row in my table.

Thanks!

 

[JLGWhiz]

OK, I am goint to have to pass on this one because my expertise with the ListObjects(Tables) is almost zero. Maybe somebody with more knowledge in that area will pick up on this thread. Give them a few hours and if no response, click 'Reply' and type 'Bump' in the reply box and then post it. after a couple of Bumps, if you don't get a reply, then start a new thread and include the additional information that you added above about the tables, name, location, size, etc. Aslo, at the bottom of this post, you will see the last word: Attachements. Click on that to get info on how to post an image of your worksheet.