VBA Code for identfying text and inserting row

[donpre]

Dear Team,

I have been trying to write the code for my below requirement with some formulas and VBA code. I need VBA code for the below case

In column C, if in any cell character is more than 3 alpha at starting point it should select that row and insert a new row below

In new row the data of column C and D should be pasted in column A and B


A B C D
AEAEY001 26780 AHFLF001 42410
RCYJS001 13600 RGK5L001 13600
AFXXE021 146707 HT178002 146707
R1346001 32611 RG627001 50000
AADD8001 84152 RIA5P001 50000
IJ814001 76470 R5624001 60000
ADLKF001 35456 RHEPR001 57699


Output should be as follow


A B C D
AEAEY001 26780 AHFLF001 42410
AHFLF001 42410 AHFLF001 42410
RCYJS001 13600 RGK5L001 13600
RGK5L001 13600 RGK5L001 13600
AFXXE021 146707 HT178002 146707
R1346001 32611 RG627001 50000
AADD8001 84152 RIA5P001 50000
RIA5P001 50000 RIA5P001 50000
IJ814001 76470 R5624001 60000
ADLKF001 35456 RHEPR001 57699
RHEPR001 57699 RHEPR001 57699


Please help me with the code.

Thanks in advance

[tigeravatar]

donpre,

Something like this should accomplish what you're looking for:

Please Login or Register  to view this content.

[donpre]

Hi,

There is one additional requirement in this Code.

I want to test the same criteria(which is tested in column c) for column E as well but when condition is met in C column it should insert only 1 row and copy that row and when condition is met in both the column then 2 row should be inserted and data should be copied in both the row and the rest remain the same.

Hope you understand what i need

Thanks

[tigeravatar]

donpre,

Can you post some before and after data with the new requirements please?
Also, what happens if the condition is met for column E but not column C?

[donpre]

Hi Tigeravator,

you mean to say i should post new thread.
if condition is met in any 1 column then it should insert only 1 row and copy the entire row data in inserted row but if condition is met in both column then it should insert 2 row and copy the entire row data in both the row.

[tigeravatar]

donpre,

You don't have to post a new thread, I'm just curious what before/after data would look like with the new requirements. In your original post, it wasn't all the data in the row that got copied to the new row. For example (taken from your original post):

AEAEY001 26780 AHFLF001 42410

became:
AEAEY001 26780 AHFLF001 42410
AHFLF001 42410 AHFLF001 42410

so only columns C and D were put in the new row (and were put in twice).
With column E to check, is there also a column F?
Does that info get put in twice in the new row as well?
Going off your most recent post, it seems like you just want the row copied, instead of just columns C and D twice, is that now the case?

[donpre]

Hi Tiger,

Below is the before data and after data
See the result in column A (Data of column C & E is overwrited and transposed in column A after the row is inserted with the original data)

Before

Column A Column B Column C Column D Column E
RCXJ0001 RBKAE001 RBJ95001
ADLMT001 RDQV0001
AFOQV001 SM783001 KJ497001
RDQ3C001 T5045001
AFE1V001 ACF6G001 AEM6600A
RC617001 R5001001

After

Column A Column B Column C Column D Column E
RCXJ0001 RBKAE001 RBJ95001
RBKAE001 RBKAE001 RBJ95001
RBJ95001 RBKAE001 RBJ95001
ADLMT001 RDQV0001
RDQV0001 RDQV0001
AFOQV001 SM783001 KJ497001
SM783001 SM783001 KJ497001
KJ497001 SM783001 KJ497001
RDQ3C001 T5045001
AFE1V001 ACF6G001 AEM6600A
ACF6G001 ACF6G001 AEM6600A
AEM6600A ACF6G001 AEM6600A
RC617001 R5001001


And let me know if i can send u the file as well in that i need to explain other thing as well.

Hope you understand it.

Thanks

[tigeravatar]

In your new data, you are copying rows if the data has only 2 consecutive alpha characters instead of three. For example:

From Before:
AFOQV001 SM783001 KJ497001

From After:
AFOQV001 SM783001 KJ497001
SM783001 SM783001 KJ497001
KJ497001 SM783001 KJ497001

But SM783001 and KJ497001 only have 2 alpha characters and not 3. If you only need 2 instead of 3, this should work for you:

Please Login or Register  to view this content.

[donpre]

Hi,

Can i send u the excel file u will understand properly in that case let me know where to send it

[tigeravatar]

You can attach a workbook to this forum. To do so, click 'Go Advanced' just under the quick reply box. Then click the Paper-Clip icon to get to the Manage Attachments window. From there you can upload a file from your computer to attach to the post.

[donpre]

Hi,

Finally this is the code i applied and it is working fine.
there is one exceptional in this code as well.

I want to test additional criteria in column AU that if any cell starting with only "Z" alpha and then after all characters are in number and in alpha( for eg Z0034D67) in that cell should perform the same action (inserting row ,copying that row etc)

I believe it will work with instr function. please help with a line of code also.

Sub Macro1 ()
Dim i As Long

Application.ScreenUpdating = False
For i = Range("AU" & Rows.Count).End(xlUp).Row To 1 Step -1
If UCase(Cells(i, "AU").Value) Like "[A-Z][A-Z][A-Z]*" Then 'Test first 3 chars for Alpha
Rows(i + 1).Insert
Rows(i).Copy Destination:=Rows(i + 1)
Cells(i + 1, "AV").ClearContents
Cells(i, "AU").Copy Destination:=Cells(i + 1, "P")
Cells(i, "AX").Resize(, 2).Copy Destination:=Cells(i + 1, "AF")

End If

If UCase(Cells(i, "AV").Value) Like "[A-Z][A-Z][A-Z]*" Then 'Test first 3 chars for Alpha
Rows(i + 1).Insert
Rows(i).Copy Destination:=Rows(i + 1)
Cells(i, "AU").Copy Destination:=Cells(i + 1, "P")
Cells(i, "AZ").Resize(, 2).Copy Destination:=Cells(i + 1, "AF")
End If
Next i
Application.ScreenUpdating = True

End Sub