Beginner to VBA looking to copy and paste row whenever ";" found in column B. Thank you

[tyantorno]

The code below does it for the first row found but does not look to any rows below that. I am assuming a loop? Thank you in advance

Option Explicit

Sub Macro2()

Dim rngAddress As Range
Dim rowToBeCopied As Integer

Set rngAddress = Range("B:B").Find(";")
If rngAddress Is Nothing Then
MsgBox "The ; in column B was not found."
Exit Sub
End If

rowToBeCopied = rngAddress.Row
Rows(rowToBeCopied).EntireRow.Insert
Rows(rowToBeCopied + 1).Copy Destination:=Rows(rowToBeCopied)

End Sub

 

[SkipVought]

hi,

Please tell us WHAT it is that you are trying to accomplish, rather than HOW it is that you think it ought to be done.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyantorno]

Hello Skip,

Thank you for the advice. I am trying to write VBA code to determine if there is a ";" in column B of an Active worksheet, and if there is any cell with a semi colon ";" in column B to copy that cell to the row below. All of the information in that row would be copied.
Thank you.

 

[SkipVought]

if there is any cell with a semi colon ";" in column B to copy that cell to the row below. All of the information in that row would be copied.

So do you want to just copy ONE cell or the entire row and paste into the next row?

If the entire row, why not just delete the next row? Would that not result in the same outcome?

You could really do this with a formula (no VBA), to put a character in a helper column in the row to delete. Then FILTER on that character and delete all the visible rows.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyantorno]

Hi Skip,

I need to keep all data intact. I just need to copy the entire row in the row below when a semi colon is in the search string on column B. Thanks

 

[mintjulep]

Copying something to the "row below" (below what?) will not preserve your data.

It will overwrite what is there now.

 

[SkipVought]

That is what is confusing to me. You will end up with 2 identical rows of data!!! How is that a helpful objective?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyantorno]

Hi Skip,

I actually do want two identical rows of data. I know it must not make sense but it is a file from an output source that is there is a semi colon in column B I need to make a copy of the line for importing into financial system. Thanks, sorry for the confusion.

 

[SkipVought]

1) sort the table on the column containing the SEMI COLON
2) Copy all the rows containing the SEMI COLON
3) paste in the row below the table
4) sort back into sequence

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, my last post was to address the "I need to make a copy of the line for importing into financial system." ignoring step 4. What I get is that you need to send the ; rows to some external financial system.

So FILTER to display only ; rows, COPY the visible rows and Edit > Paste Special - VALUES into an empty sheet that can be exported.

The confusing thing was KEEPING duplicate data in a table. THAT does not make sense. COPYING certain data, in order to PASTE to another sheet for some other purpose DOES make sense.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tyantorno]

Thank you