VBA REPLACE - too many lines in Module 1

[bojzon]

Need to replace about 10.000 items in xls column with 50.000 data. In Module I exceed maximum number lines of code. How to reduce number of lines with code? (WIN XP, Off2003). Any suggestions? (arrays?, list?,..)

Bojzon

 

[Loomah]

Seeing an extracxt of your code or at least getting a fuller description of what you are attempting to do might help a little?

I, for one, don't quite get what you mean by

Need to replace about 10.000 items in xls column with 50.000 data

If all you are doing is find and replace then is code necessary? If it is then the help file will help you. Beut without knowing more of what you are trying to do I don't think anyone will be able to help fully.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[bojzon]

In xls book with data column (50.000) numbers as text - (about 10.000 unique, others are duplicates -must not delete duplicates)I need to replace all numbers with new text (OBV, BLAG,..)

I have separate table with unique 10.000 numbers (1032226,1020051,...).They must be converted with defined, another text(1032226>OBV, 1020051>OBV, 1120009>BLAG,..).
When I create all raplaces in code, I get error: too much lines in code.

Selection.Replace What:="1032226", Replacement:="OBV ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1020051", Replacement:="OBV ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1120009", Replacement:="BLAG ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1170042", Replacement:="BLAZA ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1130112", Replacement:="OBV ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1150111", Replacement:="BLAZA ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1070050", Replacement:="BLAZA ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1070048", Replacement:="DEPO ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True
Selection.Replace What:="1050062", Replacement:="DEPO ", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True


How avoid too long code?

Thanks
Bojzon

 

[Loomah]

Is your first set of data (the 50,000 numbers) just a list on its own, is it just a column of numbers?

What does your second table look like? Is it a list of numbers with the relevant replacements next to them? What purpose does it serve? Do you need to do the same replacement exercise on this table?

Do you have a list (in Excel) showing which numbers need to be replaced with which text?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

Create a conversion table with, say, numbers in column A and corresponding text in column B.
Then in your code inside a loop in this table varying the row (say lngRow):
Selection.Replace What:=conversionSheetObj.Cells(lngRow, 1), Replacement:=conversionSheetObj.Cells(lngRow, 2), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True

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

 

[Loomah]

Which is what I was getting to!!!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​