mbnnyc
03-16-2021, 06:39 PM
Hi, I have a spreadsheet that contains mailing list data. It's in a format in which there are Alt1Email (column R), Alt2Email (column S), Alt3Email (column T), and Alt4Email (column U). Wanna cut and paste that data from those columns to the Email column (column Q) in NEW ROWs based on how many AltEmails the contact has. Then ultimately delete columns R,S,T, and U. I thought this out in a plain logic script (below) but being new to VBA I'm having trouble understand syntax a bit. Please if anyone can assist would greatly appreciate. Kind regards! Attached is the spreadsheet and screenshots. Using MS Excel 2013 for Windows and VBA 7.1 that comes with it. Thanks!
----------
{If cells in columns R,S,T,U are all Populated (not null) in the row the VBA script is working on... first row being row TWO... so R2,S2,T2,U2)
then
INSERT FOUR new rows BELOW this row
AND
COPY from that "reference" row the cell values from columns A thru P... to the corresponding columns of the 4 new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediately under the original "reference" row into COLUMN Q
AND
CUT column S value and paste to SECOND new row's Q column
AND
CUT column T value and paste to THIRD new row's Q column
AND
CUT column U value and paste to FOURTH new row's Q column}
OR
{If out of R,S,T,U.... only columns R,S, & T (only 3 alt-emails) are populated in the current "reference" row
then
INSERT THREE new rows below this row
AND
COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row
INTO COLUMN Q
AND
CUT column S value and paste to SECOND new row's Q column
AND
CUT column T value and paste to THIRD new row's Q column}
OR
{If out of R,S,T,U, only R & S (only 2 alt-emails) are populated in that "reference" row
then
INSERT TWO new rows below the reference row
AND
copy from that "reference" row the cell values from columns A thru P... and PASTE to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row to COLUMN Q
AND
CUT column S value and Paste to SECOND new row's Q column}
OR
{If out of R,S,T, U, only column R is populated in that particular row)
then
INSERT ONE new row below the reference row
AND COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row into COLUMN Q}
{If we just added 4 new rows, move down FIVE rows from the original row... if this new “reference” row is a totally blank row, END this script,
If not, make THIS row the new “reference” row AND go back to beginning of script
if we just added 3 new rows, move down FOUR rows from the original row... if this is a totally blank row, END this script
If not, make THIS row the new “reference” row AND go back to beginning of script
{if we just added 2 new rows, move down THREE rows from the original row... if this is a totally blank row, END this script,
If not, make THIS row the new “reference” row AND go back to beginning of script}
{if we just added 1 new row, move down 2 rows from the original row... if this is a totally black row, END this script, if not, make THIS row the new criteria row}
{if we added no new rows in the above process, move down to the next row below original row. if this is a totally BLANK NULL row, END this script,}
Go back to beginning criteria of this script
----------
{If cells in columns R,S,T,U are all Populated (not null) in the row the VBA script is working on... first row being row TWO... so R2,S2,T2,U2)
then
INSERT FOUR new rows BELOW this row
AND
COPY from that "reference" row the cell values from columns A thru P... to the corresponding columns of the 4 new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediately under the original "reference" row into COLUMN Q
AND
CUT column S value and paste to SECOND new row's Q column
AND
CUT column T value and paste to THIRD new row's Q column
AND
CUT column U value and paste to FOURTH new row's Q column}
OR
{If out of R,S,T,U.... only columns R,S, & T (only 3 alt-emails) are populated in the current "reference" row
then
INSERT THREE new rows below this row
AND
COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row
INTO COLUMN Q
AND
CUT column S value and paste to SECOND new row's Q column
AND
CUT column T value and paste to THIRD new row's Q column}
OR
{If out of R,S,T,U, only R & S (only 2 alt-emails) are populated in that "reference" row
then
INSERT TWO new rows below the reference row
AND
copy from that "reference" row the cell values from columns A thru P... and PASTE to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row to COLUMN Q
AND
CUT column S value and Paste to SECOND new row's Q column}
OR
{If out of R,S,T, U, only column R is populated in that particular row)
then
INSERT ONE new row below the reference row
AND COPY from that "reference" row the cell values from columns A thru P... and paste into to the corresponding columns of the THREE new rows just created,
AND
CUT cell value from column R AND PASTE IT to the FIRST new row immediedly under the original "reference" row into COLUMN Q}
{If we just added 4 new rows, move down FIVE rows from the original row... if this new “reference” row is a totally blank row, END this script,
If not, make THIS row the new “reference” row AND go back to beginning of script
if we just added 3 new rows, move down FOUR rows from the original row... if this is a totally blank row, END this script
If not, make THIS row the new “reference” row AND go back to beginning of script
{if we just added 2 new rows, move down THREE rows from the original row... if this is a totally blank row, END this script,
If not, make THIS row the new “reference” row AND go back to beginning of script}
{if we just added 1 new row, move down 2 rows from the original row... if this is a totally black row, END this script, if not, make THIS row the new criteria row}
{if we added no new rows in the above process, move down to the next row below original row. if this is a totally BLANK NULL row, END this script,}
Go back to beginning criteria of this script