Finding, printing and counting substrings in VBA

[Argonath]

If I have a worksheet with alphanumeric string data in columns like:

Column A
--------
Row 1 %4(_XY76
Row 2 ^&*_XY11
Row 3 *_XY7-
Row 4 *()_XY7Y
Row 5 *)(_XY11

What code can I write that will (1) find the occurrence of the substrings "_XY7" and "_XY11", (2) count the number of occurrences of each substring and (3) put the output in Columns B-E as follows:

Column B Column C Column D Column E
-------- -------- -------- --------
Row 1 XY7 1
Row 2 XY11 1
Row 3 XY7 2
Row 4 XY7 3
Row 5 XY11 2

In other words, the rows where Column A contains the substring "_XY7" are counted, the substring itself is output in Column B and the count is output in Column C (on the same row that "_XY7" is found). Similarly, the rows where Column A contains the substring "_XY11" are counted, the substring is output to Column D and the count is output to Column E (on the same row that "_XY11" is found). Assume that "_XY7" and "_XY11" do not occur on the same row.

Thanks for any help in advance!

 

[PHV]

No VBA needed at all, just formulas:[tt]
Cell Formula
B1 =IF(ISERROR(SEARCH("XY7",A1,1)),"","XY7")
C1 =IF(B1="XY7",COUNTIF(B$1:B1,"XY7"),"")
D1 =IF(ISERROR(SEARCH("XY11",A1,1)),"","XY11")
E1 =IF(D1="XY11",COUNTIF(D$11,"XY11"),"")[/tt]

Then copy down ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886