There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Tag Cloud
access audio avg avg 8 bios blue screen boot bsod computer connection cpu crash css dell desktop dma driver drivers dvd email error excel explorer firefox firefox 3 freeze gimp graphics hard drive hardware hijackthis hjt install internet internet explorer itunes keyboard laptop macro malware monitor motherboard network networking outlook outlook 2003 outlook 2007 outlook express pio problem problems router seo server slow sound sp3 spyware trojan usb video virtumonde virus vista vundo windows windows vista windows xp winxp wireless
Software Development
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Software Development >
VBA/Excel Help - I'm stressed!


HELLO AND WELCOME! Before you can post your question, you'll have to register -- it's completely free! Click here to join today! We highly recommend that you print a copy of our Guide for New Members. Enjoy!

 
Thread Tools
TerriG's Avatar
Junior Member with 3 posts.
 
Join Date: Jun 2003
27-Jun-2003, 03:01 PM #1
Unhappy VBA/Excel Help - I'm stressed!
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's Avatar
Senior Member with 310 posts.
 
Join Date: Dec 2000
Location: Charlotte, NC USA
29-Jun-2003, 05:30 PM #2
welcome to the Twilight Zone
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's Avatar
Junior Member with 3 posts.
 
Join Date: Jun 2003
29-Jun-2003, 05:38 PM #3
Thanks for the information, but I have to use Excel. :-(
AngryClip's Avatar
Senior Member with 315 posts.
 
Join Date: Jun 2002
Location: England
04-Jul-2003, 01:19 AM #4
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.
__________________
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS/O dpu(-)@ s++:+ a-->? C+++>--@ UL>B++++ P+>++++ L+(--) E--- W++>+++ N
o? K- w+++>+++++$ O--->---- M->-- V? PS+ PE Y? PGP--- t+(-) 5? X-(++) R*
tv++>! b+ DI+++>++++>+++++ D++ G>+++ e*>+++++ h!(*)>++ r%>$ x+>$
------END GEEK CODE BLOCK------
AngryClip's Avatar
Senior Member with 315 posts.
 
Join Date: Jun 2002
Location: England
04-Jul-2003, 01:27 AM #5
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.
__________________
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCS/O dpu(-)@ s++:+ a-->? C+++>--@ UL>B++++ P+>++++ L+(--) E--- W++>+++ N
o? K- w+++>+++++$ O--->---- M->-- V? PS+ PE Y? PGP--- t+(-) 5? X-(++) R*
tv++>! b+ DI+++>++++>+++++ D++ G>+++ e*>+++++ h!(*)>++ r%>$ x+>$
------END GEEK CODE BLOCK------
TerriG's Avatar
Junior Member with 3 posts.
 
Join Date: Jun 2003
06-Jul-2003, 08:38 PM #6
Talking Worth a shot!
Hi there!

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

Take Care!
AngryClip's Avatar
Senior Member with 315 posts.
 
Join Date: Jun 2002
Location: England
06-Jul-2003, 09:10 PM #7
No problem

I hope it does :P
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are Off
Refbacks are Off

You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 05:44 AM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.