Filter out one list of text in another list.

  • Thread starter Thread starter Géry Duyck
  • Start date Start date
G

Géry Duyck

Hi,

My problem is the folowing.
I'm a radio amateur and I want to choos a good callname.
consist of a prefix followed by 3 or 2 letters.

On the site of our department that gives the calls I found a list with all
the used calls (in excel format).
I want to revers it!

With some trick's I managed to create a huge list with all the calls
starting from AA to ZZZ
(there are 18252 in total!)

Now I want to filter out the calls that are already in use, so I will only
see the free calls.

I'm searching now the whole night and I can't find any good function that
does this.
I started recording a macro.

This is my idee:
- create a map with 2 sheets, sheet 1: all the calls, sheet 2: used calls
- go to sheet 2 and select the first from the list
- start recording macro
- copy that value
- go to sheet 1 and press Ctrl+F, and choose replace
- paste the value
- enter BUSY as replacement
- go back to sheet 2 and select the 2nd from the list
- stop recording macro

Now it should by pressing the macro's keyboard shortcut replace all the busy
calls by BUSY
It doesn't :(

I think my problem is in the copy - past, I get the following source code of
my macro

Sub change()
'
' Change Macro
' Recorded 4/09/2003 by Gery Duyck.
'
' Shortcut: CTRL+V
'
Range("A1").Select
Selection.copy
Sheets("Sheet2").Select
Range("A1").Select
Cells.Replace What:="AAA", Replacement:="BUSY", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("Sheet3").Select
Range("A2").Select
End Sub


You see? he just pasted the value (AAA) in stead of a paste command in the
source code.
Can somebody help ???
How can I get this script working or is there a better way to do this.

Kind Regards,
Gery D.
 
This wors with a call or 20.
But with a list of aprox 18500 calls it crashes.

either crashes excel,
or sometimes give me only 0 or only 1's

Gery Duyck
 
Back
Top