Need VBA to locate two specific characters the first time they appear together in the data and enter all data that precedes those two specified charac

[skyport]

If I use the paragraph below as an example, the two specific characters to look for would be the first time there is a period (.) followed by a space. In the example below, those characters together occur once around the middle of the paragraph and at the end as well.

Example Paragraph:
Need VBA to locate two specific characters the first time they appear together in different paragraphs in column A. It would then enter all data that comes before those two specified characters to another column-B. Thanks.

The idea behind what is needed for the code to do the job would be to find that first occurrence of the two characters within that paragraph in Column A and then enter all the data that comes before those two characters in that paragraph and enter the result in the next column -B in a cell by itself.

Using the above example paragraph, the result in a cell by itself in column B would be as follows:

Need VBA to locate two specific characters the first time they appear together in different paragraphs in column A.

The result is the data in the paragraph from column A only up to the first occurrence of those two characters together is entered in a cell by itself in column B. The code should then repeat that process for each subsequent paragraph in column A using a separate cell in column B for each result. Note: Every paragraph would be separated from the paragraph above and below with a blank line.

 

[ask2tsp]

@Rick: I try to figure out how your code works but I do not understand the @A1:A# syntax. Is this array processing? Can you point me to articles on that?

 

[skyport]

Rick, It worked perfectly and got the first line as well to resolve that issue. I want to thank both of you for your kind help.

 

[Rick Rothstein]

@Rick: I try to figure out how your code works but I do not understand the @A1:A# syntax. Is this array processing? Can you point me to articles on that?

The @ symbol is a stand-in character for the sheet name reference and the # symbol is a stand-in character for the last row number (which was calculated earlier in the macro... any two characters would do so long as they do not appear in the rest of the text naturally. See the two Replace function calls... they do the replacements for those characters so that the final argument to the Evaluate function is correct. I do it using the replacement because the makes the text easier to construct than would be the case if I concatenated the sheet name reference variable and last row variable directly in the text all three times each are needed there.

 

[ask2tsp]

Change shtRow = 2
to shtRow = 1

to correct this.