Find - replace

[joeny0706]

Hi all

I have a find and replace module and need some helpmodifying it.
I have a vba module that looks at one excel sheet and thenit uses that to convert text. Right now it needs to find the exact string.

Is there a way I can make it convert if it finds only oneword In the find cell?


The current code looks at the sheet and if it finds what isin column D it replaces it with what is in column E. The problem is I will not alwaysknow the city and number included in the text it is finding.. If i could makeit so if it finds the word shoprite anywhere within it will then convert itinto the text in column E


Ex
Column D Column E

-0628 Shoprite #628-Middletown	Vermont Bread:ShopRite Supermarkets Inc.
-0641 Shoprite #641-Bricktown	Vermont Bread:ShopRite Supermarkets Inc.
-0299 Shoprite #299-Neptune	Vermont Bread:ShopRite Supermarkets Inc.
-0112 Shoprite #112-Staten Island	Vermont Bread:ShopRite Supermarkets Inc.
-0497 Shoprite #497-Clinton	Vermont Bread:ShopRite Supermarkets Inc.

I attached an excel sheet that shows the currentconversions. I have 8 different groups that could be changing by adding anumber or city. But it will always include the main company name “kings orshoprite or wild by nature etc”



If it finds the work “kullen” it will always convert that to “Vermont Bread:King Kullen Grocery Co. Inc”.
If it finds the work “kings” it will convert to “Vermont Bread:KingsSuper Markets” and so on.

Independent	Vermont Bread:Independent
Wegmans Food Markets	Vermont Bread:Wegmans Food Markets
ShopRite Supermarkets Inc.	Vermont Bread:ShopRite Supermarkets Inc.
Speziale LTD (bulk order)	Vermont Bread:Speziale LTD (bulk order)
King Kullen Grocery Co. Inc.	Vermont Bread:King Kullen Grocery Co. Inc.
Wild By Nature Market	Vermont Bread:Wild By Nature Market
Kings Super Markets	Vermont Bread:Kings Super Markets
Whole Foods Market	Vermont Bread:Whole Foods Market

Now they will be adding A city and number within the find cell

-0112 Shoprite #1-Staten Island

-0628 Shoprite #6-Middletown

-0628 Shoprite #8-Middletown

-0112 Shoprite #2-Staten Island

The code for the module I use now is in the attached wordfile.





Thank you all



I was not able to find what to modify and still have itwork. I tried but everytime I got an error

[大灰狼1976]

Hi joeny!
Is it OK to integrate the contents of column d:
-0628 Shoprite #628-Middletown
-0641 Shoprite #641-Bricktown
-0299 Shoprite #299-Neptune
-0112 Shoprite #112-Staten Island
-0497 Shoprite #497-Clinton
into a "Shoprite"?

[joeny0706]

Hi joeny!
Is it OK to integrate the contents of column d:
-0628 Shoprite #628-Middletown
-0641 Shoprite #641-Bricktown
-0299 Shoprite #299-Neptune
-0112 Shoprite #112-Staten Island
-0497 Shoprite #497-Clinton
into a "Shoprite"?

No. I need it so when If finds the word shoprite anywhere within the cell to remove all contents and replace it with “Vermont Bread:ShopRite Supermarkets Inc.”

Then I need the same for the other ones “kings, wegmans etc”


Thank You¡¡

[大灰狼1976]

Hi joeny!
No problem, modify the data as I said, and then modify the following code.

ws.UsedRange.Cells.Replace What:="*" & r1.Cells(i, 1).Value & "*", Replacement:=r1.Cells(i, 2).Value, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False

[大灰狼1976]

And, "ShopRite" is included in the data below, but the case is different.
"ShopRite Supermarkets Inc."　→　"Vermont Bread:ShopRite Supermarkets Inc."
In order to be case sensitive, you need to set:

SearchFormat:=True

[snb]

@..1976
Too much redundancy in your code.
Use arrays

sn=cells(1).currentregion
  for j=1 to ubound(sn)
    columns("D:E").Replace sn(j,1), sn(j,2),2
  next

[joeny0706]

I am not sure I am explaining this correctly. So maybeshowing you what I currently am doing will help.


Attached is the data I receive. I then currently copy andpast that data into the xlsm file I use to do the conversion. Also the xlsxfile that uses to do the find and replace. Then the end results are attached. Inthe end results file you will see how I need each shoprite to be the same.
The data I receive has changed. It did not have the city andnumber included in the past so it all worked perfect. Now with the city andnumber I have to manually changed it.

Thanks you for helping





The modules included I had others make for me. I just havealtered some very little and put the xlsm file together with them all. I don’t knowmuch about writing them

[snb]

I'd use this macro in the 'find and replace data.xlsx filte:

Sub M_snb()
  sn = Cells(1).CurrentRegion
  sp = Cells(1, 4).CurrentRegion
  
  c00 = CreateObject("scripting.filesystemobject").opentextfile("G:\OF\data I receive.csv").readall
  
  c00 = Replace(Replace(Replace(c00, "      ", "  "), "  ", ","), ",-", "")
  
  For j = 2 To UBound(sn)
    If j <= UBound(sn) Then c00 = Replace(c00, sn(j, 1), sn(j, 2), , , 1)
    If j <= UBound(sp) Then c00 = Replace(c00, "," & sp(j, 1) & ",", "," & sp(j, 2) & ",", , , 1)
  Next
  
  sq = Split(c00, vbCrLf)
  
  With Sheets.Add(, Sheets(Sheets.Count))
    .Cells(1).Resize(UBound(sq)) = Application.Transpose(sq)
    .Columns(1).TextToColumns , , , , 0, 0, -1, 0
 End With
End Sub

[joeny0706]

I may not of explain what happens good.


I get an email each day that is the orders a customer isplacing. That is the file I attached "data I receive.csv". This filehas the date as the file name each day. This file has different stores anddifferent items each time. The store names before where generic names “all ofthe shoprite rows had the same name”. They did not include the city and anumber. So the xlsm file I used to convert that data into a format I can use toupload the orders into my ERP program. So I copy and paste the data from"data I receive.csv" into "file I use to do theconversion.xlsm" The xlsm file has macros to do the conversions. One ofthe macros will compare the data in the data I receive with the data in “findand replace data”.xlsx If it finds anything in the data I receive that matchesany of the words in column D it will replace it with what is in column E.






Starting last week the store names started to include thecity and number. So my find and repace does not work for the customer names. Thenames change each time so I don’t know what they will be.






So each time I get the data the customer name does alwaysinclude the chain name. It also has the city and number but if I can have afind and replace macro the searches and if it finds a cell with the wordshoprite it will clear that data out and replace it with “Vermont Bread:ShopRiteSupermarkets Inc.”


Ialso need the same thing for kings. So if it finds a cell with the word kingsin it then it will clear out the cell and enter “Vermont Bread:Kings SuperMarkets”


Ineed that for all the others also “King kullen, Wegman, bulk order, etc”



Isthere anyway to modify my macro so if it finds one word that matches it willclear out the cell and enter what I want.






Ihave created an excel sheet that shows what would be great. “find and replace datamodified.xlsx” If you look in column D that has the words it needs to searchfor. And if it finds any of those words in column D it will clear out the celland fill it with what is in column E to the right of the word it finds.







If itcould turn all of the below into Vermont Bread:ShopRite Supermarkets Inc.

-0112 Shoprite #112-Staten Island

-0637 Shoprite #637-Belmar

-0628 Shoprite #628-Middletown

-0641 Shoprite #641-Bricktown

-0299 Shoprite #299-Neptune

-0112 Shoprite #112-Staten Island

-0497 Shoprite #497-Clinton

Thatwould be great.









Thankyou all for working with me

[大灰狼1976]

@snb
Thank you for your guidance, but I don't like to change other people's code,
only to make minimal changes to the original code, thank you again!

--Okami

[joeny0706]

Hi joeny!
No problem, modify the data as I said, and then modify the following code.

ws.UsedRange.Cells.Replace What:="*" & r1.Cells(i, 1).Value & "*", Replacement:=r1.Cells(i, 2).Value, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
                SearchFormat:=False, ReplaceFormat:=False

I am not sure what data you are saying to modify. And what that will do. Can you explain a bit

Will it then turn anything that includes shoprite into the Vermont bread shop right statement?
ty

[joeny0706]

Does anyone know why when I am posting a reply from a computer it keeps doing auto save and cause it so when I type to skip keys that I press. From my phone it works fine

i always end up writing g the reply in word and pasting it into the reply box.

[snb]

It is definitely not the TS's code.

@snb
Thank you for your guidance, but I don't like to change other people's code,
only to make minimal changes to the original code, thank you again!

--Okami

[大灰狼1976]

@joeny
Combine all data like "-???? Shoprite #???-$$$$$$$$"into one "*Shoprite*" (refer to the picture).
Don't need to change any of your code unless you're case sensitive.
sample.jpg

[joeny0706]

Thank You¡¡.
I must not be explaining what I need very good. I apologize. Or I am not understanding what you are saying and the purpose of that. I don’t see how changing the data in the file that is the find and replace map will help me.

That kinda defeats the purpose of why I started this thread. I am trying to get this data converted daily by just running one macro. No typing or modifications before they made the change of adding the city into the customer name that was all I had to do. But now since that has been added I have to manually changed it.

Is it possible to replace the contents in a cell by only finding one word. Since all the ones I need changed do include the word shoprite but they also include other data I need removed. I am not sure how else to explain than how I did above.

[大灰狼1976]

Are you sure it doesn't work?
A simple example is attached for testing.

[joeny0706]

The whole time this was such a simple anwser. I apologize I was not understanding what you Where saying. I knew if I put just shoprite it would only replace that word. I did not realize putting the * was the anwser.
Thank you. Again I apologize for all the back and forth I was doing.


When you said
Is it OK to integrate the contents of column d:
-0628 Shoprite #628-Middletown
-0641 Shoprite #641-Bricktown
-0299 Shoprite #299-Neptune
-0112 Shoprite #112-Staten Island
-0497 Shoprite #497-Clinton
into a "Shoprite"?
that is what confused me.

When this whole time all i needed to do was add the *

Thank You¡¡

[大灰狼1976]

@joeny!
I'm sorry my English is not very good.
Has the problem been solved now?


--Okami

[joeny0706]

@joeny!
I'm sorry my English is not very good.
Has the problem been solved now?


--Okami

Yes it has

ty

[大灰狼1976]

@joeny
You're welcome, and thank you for your feedback.