Extremely slow macro
 

Hi All...........

I have a macro that works fine, except it takes too long to
run......something like a couple of minutes. The thing that makes it slow
is the following line.........

Worksheets("BladesX1").Range("FieldBladesX1").Form ula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))"

It's all one line, the word-wrap is playing it here.........
The MasterNamefile sheet is a database of some 15,000 lines. The
FieldBladesX1 contains some 1000+ cells. I know it's doing a lot of work,
and it does exactly as it's supposed to, but is there any way to speed it up
any?

TIA
Vaya con Dios,
Chuck, CABGx3

Extremely slow macro
 

Hi Chuck

Maybe

"=IF(COUNTIF(MasterNamefile!$B:$B,$C8&""_""&D$1&"" _""&D$4),
VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$ B:$S,14,FALSE), """")"


--
Regards

Roger Govier


"CLR" wrote in message
...
Hi All...........

I have a macro that works fine, except it takes too long to
run......something like a couple of minutes. The thing that makes it
slow
is the following line.........

Worksheets("BladesX1").Range("FieldBladesX1").Form ula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))"

It's all one line, the word-wrap is playing it here.........
The MasterNamefile sheet is a database of some 15,000 lines. The
FieldBladesX1 contains some 1000+ cells. I know it's doing a lot of
work,
and it does exactly as it's supposed to, but is there any way to speed
it up
any?

TIA
Vaya con Dios,
Chuck, CABGx3

Extremely slow macro
 

Thanks Roger.........
I'll have to give it a try tomorrow.........for some reason I keep getting
"Out of memory" error tonight here on this machine when I try to go into the
VBE.........

Vaya con Dios,
Chuck, CABGx3


"Roger Govier" wrote in message
...
Hi Chuck

Maybe

"=IF(COUNTIF(MasterNamefile!$B:$B,$C8&""_""&D$1&"" _""&D$4),
VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$ B:$S,14,FALSE), """")"


--
Regards

Roger Govier


"CLR" wrote in message
...
Hi All...........

I have a macro that works fine, except it takes too long to
run......something like a couple of minutes. The thing that makes it
slow
is the following line.........

Worksheets("BladesX1").Range("FieldBladesX1").Form ula =

"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))"

It's all one line, the word-wrap is playing it here.........
The MasterNamefile sheet is a database of some 15,000 lines. The
FieldBladesX1 contains some 1000+ cells. I know it's doing a lot of
work,
and it does exactly as it's supposed to, but is there any way to speed
it up
any?

TIA
Vaya con Dios,
Chuck, CABGx3

Extremely slow macro
 

Hi Chuck

Glad to have been of service.
Compensates just a little for all that I have gained from your postings
over the past few years!!

--
Regards

Roger Govier


"CLR" wrote in message
...
Yes Sireeee Roger.....YOU 'DA MAN!!!!........I just got to work and
tried
your solution on my computer here and it worked perfectly. My old
version of
the macro took about 2 minutes to run that particular one, and your
improvement cut that to 44 seconds. A significant benefit,
considering I
have over 40 of these things to run.

Many, many Thank you's.....
Vaya con Dios,
Chuck, CABGx3




"Roger Govier" wrote:

Hi Chuck

Maybe

"=IF(COUNTIF(MasterNamefile!$B:$B,$C8&""_""&D$1&"" _""&D$4),
VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamefile!$ B:$S,14,FALSE),
"""")"


--
Regards

Roger Govier


"CLR" wrote in message
...
Hi All...........

I have a macro that works fine, except it takes too long to
run......something like a couple of minutes. The thing that makes
it
slow
is the following line.........

Worksheets("BladesX1").Range("FieldBladesX1").Form ula =
"=IF(ISNA(VLOOKUP(($C8&""_""&D$1&""_""&D$4),Master Namefile!$B:$S,14,FALSE)),
"""",VLOOKUP(($C8&""_""&D$1&""_""&D$4),MasterNamef ile!$B:$S,14,FALSE))"

It's all one line, the word-wrap is playing it here.........
The MasterNamefile sheet is a database of some 15,000 lines. The
FieldBladesX1 contains some 1000+ cells. I know it's doing a lot
of
work,
and it does exactly as it's supposed to, but is there any way to
speed
it up
any?

TIA
Vaya con Dios,
Chuck, CABGx3