VBA Find_Replace

[Jpa53]

I have a script to find and replace BTO bird abbreviations for their common name i.e. "BC" is "Blackcap". I have tried two approaches , both using using an array. It works on the whole though I have had to use full stops (periods) in some cases, but with some interesting results.

This is what I am trying to replace on Sheet1. Not all rows are the same length.

7Apr 2CD R. 2B.-fm
8Apr C. MG 1B.-m
9Apr 2BT 2MG 1B.-m 2R. WP C. CD


and this is the result, just columns A-D

07-Apr 2Crowollared Dunnockove 2Collared Dove Robin
08-Apr Crow. Crow Magpie
09-Apr 2Blue Tit 2Blue Tit 2Magpie

Column B is a duplicate of C but with errors. Column C is what should have appeared in column B. The rest of the columns are fine. I am new to vba and modified a script I found on the internet but am struggling after many hours to put this right. I suspect the problem is with the loop.

The Script

Sub FindReplaceAll()

Application.ScreenUpdating = False
Dim v As Variant, i As Long

v = Array("B.", "Blackbird", "BC", "Blackcap", "BT", "Blue Tit", "BF", "Bullfinch" _
, "CG", "Canada Goose", "CH", "Chaffinch", "CC", "Chiffchaff", "CT", "Coal Tit" _
, "CD", "Collared Dove", "BZ", "Common Buzzard", "C.", "Crow", "D.", "Dunnock" _
, "GO", "Goldfinch", "GS", "Great Spotted Woodpecker", "GT", "Great Tit" _
, "GF", "Greenfinch", "H.", "Grey Heron", "HM", "House Martin", "HS", "House Sparrow" _
, "JD", "Jackdaw", "J.", "Jay", "K", "Kestrel", "LR", "Lesser Redpoll" _
, "LT", "Long Tailed Tit", "MG", "Magpie", "M.", "Mistle Thrush", "NH", "Nuthatch" _
, "PE", "Peregrin Falcon", "KT", "Red Kite", "RE", "Redwing", "R.", " Robin" _
, "SK", "Siskin", "ST", "Song Thrush", "SH", "Sparrowhawk", "SG", "Starling" _
, "SD", "Stock Dove", "SL", "Swallow", "SI", "Swift", "WW", "Willow Warbler" _
, "WP", "Wood Pigeon", "WR", "Wren")

For i = LBound(v) To UBound(v) - 1 Step 2
ActiveSheet.UsedRange.Replace v(i), v(i + 1), , , vbTextCompare
Next i
Application.ScreenUpdating = True

End Sub

Help would be gratefully appreciated.

[CheeseSandwich]

Are you able to share the file, there is a yellow banner at the top of this page that explains how?

Would be interesting to see the layout, must be quite hard to manage that array - first thoughts would be to convert that array to a lookup table with two columns - would be easier to manage any additions.

[Jpa53]

I've attached a file, I think. I was hoping it was just a glitch in the loop but I'm open to any suggestions.
Thanks for taking the time to look at it.

[oeldere]

Non VBA solution.

Power Query and VLookup Table.

You did not add all species in the array.



The yellow cells contains a formula.


See the attached file.

[Jpa53]

Your formula works well for the abbreviations but unfortunately cuts out the additional information i.e. the number and gender of the bird or birds and also whether it is a juvenile, and returns N/A. You've also transposed the information into one column for the formula to work.

I need to keep the formating, dates on rows and all relevant information as this then goes onto other sheets where it can be analysed.

I have not included all the birds just the ones that turn up in my patch.

Thanks all the same for your efforts.

[oeldere]

With this data you can analyse the data.


i.e. the number and gender of the bird => I don't see that data in your file.

juvenile => I don't see that data in your file.

and returns N/A. => the data is not in your array.

[Jpa53]

If you look at column D for 07-Apr, on my file, you will notice "2B.-mf" which is my way of saying I saw "2 Blackbirds" one "male" the other "female". Your formula returns N/A for that cell. Once that information is transfered to another sheet as "2Blackbird-mf" I can then use a formula e,g.
=IF(LEN(B4)-LEN(SUBSTITUTE(B4,"m",""))=0,"",LEN(B4)-LEN(SUBSTITUTE(B4,"m",""))) to count how many, in this case "male" birds, that I have seen.

The Data is for April so there are no "j"s as there are few if any juvenile birds around but there are later in the year.

Sorry for the confusion, I hope I have made myself a little clearer.

[oeldere]

Changed the data with text to column.

See the attached file with an example of a pivot table (sheet pivot table).

[Artik]

Jpa53, You seem to have mixed up the Range.Replace METHOD with the VBA.Replace FUNCTION. In your case, you should use the method.
Note that whether you use the Replace method manually or with code, the settings are remembered until Excel is closed. Since we are never sure if someone hasn't used the method just now, you should explicitly declare all arguments to set the replacement exactly with your expectations.

Please Login or Register  to view this content.

Once that information is transfered to another sheet as "2Blackbird-mf" I can then use a formula e,g.
=IF(LEN(B4)-LEN(SUBSTITUTE(B4,"m",""))=0,"",LEN(B4)-LEN(SUBSTITUTE(B4,"m",""))) to count how many, in this case "male" birds, that I have seen.

I'm afraid you may count more birds than you actually saw. For males, for example, you saw "1 Common Buzzard-m" and the formula will count you 3 guys. It will be even worse with girls, e.g. "2 Chiffchaff-ff". You'll get quite a flock.

Artik

[Jpa53]

This project is from another free app that I have been using for some time where macros, ugly ones I admit as I had to resort to recording and then editing them, do work in harmony with functions to achieve precisely what I need having spent a lot of time researching the necessary information but as I have invested in one drive as a storage facility and Microsoft 365 comes as a bonus. I am currently trying to transfer my limited knowledge from one app to the other. Of course this is not easy as vba and the macros in the other app do not work in the same way. As much as I enjoy the challenge and have already spent many hours on this before coming to the forum, I'm hoping to keep the transfer as simple as possible as programming is not my main interest, but this does help simplify recording my bird observations and it is unlikely that I will need it for anything else.

Thanks oeldere you appear to have come up with a solution which should work once I understand pivot tables though I cannot see how you arrived at the contents of columns C and D on the sheet "Table 1." There doesn't appear to be a function related to them and though they appear in the pivot table list the boxes are not ticked. I could see a work around for that and I may have to resort to your method. The vlookup table seems to be the key as you first suggested.

[Jpa53]

Thanks artik your script works perfectly keeping the information in the format I had hoped and for pointing me in the direction of some interesting items which may be of further help. I had already tried to search the forum for such information before joining it but obviously was not asking the right questions.

[Jpa53]

Thanks artik your script works perfectly keeping the information in the format I had hoped and for pointing me in the direction of some interesting items which may be of further help I had already tried to search the forum for such information before joining it but obviously was not asking the right questions.

[Artik]

Still returning to the formula counting the number in a particular gender. The formula should take into account only the part of the text after the hyphen. Since it would be too complex for me, I would use my own function (UDF):

Please Login or Register  to view this content.

Call in the sheet:

Please Login or Register  to view this content.

where C10 contains eg. "3Blackbird-mmf"

Artik

[Jpa53]

Thanks once again artik, I shall look at that in detail. Up until now I have used the function "=IFERROR(MID(A5,FIND("-",A5)+1,99)," ")" to separate m, f and j into their own column and then the function mentioned above, in an earlier post, altering the key letter to give me the count. Yours does look the neater solution. I have one more problem which I need to spend time on to try and resolve myself but I may be back.