[TerriG]

Hi. I have a spreadsheet that lists our products, what country (Column S in workbook) they are shipped from, etc. I start with a Master worksheet which has all our products. I created worksheets for Africa, Americas, Asia, Australia, Europe, and Misc. Then I sort the master list by country code (ie., US=America, DE=Germany, etc). and then I copy and paste into the appropriate worksheet. Then I total up counts for the
products we sell in each country. Some of our products are in excess of 40,000 units and it can take me up to 2 days to do the sorting and counting.

Here's my code so far...(only the AU one converts)...

Sub ReplaceDE()
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer

ThisWorkbook.Worksheets(1).Activate
Set rFound = Columns(19).Find("DE")
Set rFound = Columns(19).Find("AU")

iCount = 0
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value, _
"DE", "Germany")
rFound.Value = Application.Substitute(rFound.Value, _
"AU", "Australia")

iCount = iCount + 1
Set rFound = Columns(19).Cells.FindNext(rFound)
Loop
MsgBox "Replaced occurrences in " & iCount & " cells."
End Sub


Sub ReplaceCC()

End Sub

[nczman]

Personally, I would do it in Access. I do a search of around 191,000 records and it only takes 10-15 minutes.

try http://tek-tips.com/ and see if they might be able to help. They have helped me on VBA. Also, DreamBoat monitors Business Applications. She has a web site

http://thewordexpert.com/

Dreamboat knows quite a bit about MS Office products.

[TerriG]

Thanks for the information, but I have to use Excel. :-(

[AngryClip]

Thre reason your function is only working for AU is that on line 8:
Set rFound = Columns(19).Find("DE")
you assign a value to "rFound" and then on line 9
Set rFound = Columns(19).Find("AU")
You clear that value and assign it the AU one.

Thats about all the help I can give you (haven't used VBA for a while)

Sorry I could not be any more helpful.

[AngryClip]

From what I can remember may this will work;
[vbcode]
Sub ReplaceDE(SearchingFor As String, ReplaceWith As String)
Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer

ThisWorkbook.Worksheets(1).Activate
Set rFound = Columns(19).Find(SearchingFor)


iCount = 0
Do While Not rFound Is Nothing
''' Store address of first occurrence
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do ''' If we have looped around, quit
End If
rFound.Value = Application.Substitute(rFound.Value, SearchingFor, ReplaceWith)

iCount = iCount + 1
Set rFound = Columns(19).Cells.FindNext(rFound)
Loop
MsgBox "Replaced occurrences in " & iCount & " cells."
End Sub


Sub ReplaceCC()

End Sub
[/vbcode]

an example of how you would call this sub to replace the extensions with the name is like thus;

[vbcode]
Call ReplaceDE("AU", "Australia")
Call ReplaceDE("DE", "Germany")
[/vbcode]

and so on...


Its been a while so I would maybe wait until someone gives a confirmation, or provides a better solution.

[TerriG]

Hi there!

Thanks for the code. I'll try it and let you know how it works.

Take Care!

[AngryClip]

No problem

I hope it does :P