Problem whit a macro

[Ismael]

Hi to all again,



This time I have a problem whit a macro that I have made.

When I run the macro a strange thing happen, as you can see in the attached file I have to file one that is called "Main file" (were is the macro) and another one that is called "File to run the Macro".

So if you run the macro in the "file to run macro" you will see that a message will appear during the process, I put this message, because the problem happens in this point. As you can see in this point a formula appears on cell B8 until were everything ok, but then I what pull down this formula until cell B25000, but the macro instead of that is delete all the content of cell.

I just don't understand why this is happens. So if you guys can give me a help I appreciate.

Best Regards.

Ismael

[Ismael]

Hi,

Do you guys having a suggestion for this?

Thanks.

Best regards,

Ismael

[TonyJollans]

Hi Ismael,

The code you have looks like you got it from the Macro Recorder. What can I say, except that the macro recorder isn't perfect.

You need to give VBA a bit of help, so instead of ...


Range("B8").Select
Selection.FillDown

Try ...


Range("B8:B25000").FillDown

Note that you do not need to, and generally are better not to, Select the Range in order to fill down.

[Ismael]

Hi Tony,

In first place thanks for the help. Now it's working. It's was a silly error end I couldn't find' it.

But just one more question I will use this macro to run files that have 25000 (or more) rows of data, end this turns the excel very slow, do you now any hay to do this more faster, or accelerate the macro.

best regards,

Ismael

[TonyJollans]

Hi Ismael,

I don't have the code in front of me but the first thing I would do, as I hinted at above, is get rid of as much Selection as you can and work directly on Ranges. Also set Application.ScreenUpdating to False while the code is running.

If, after you have done that, you still have performance problems then post back and I or someone else may be able to identify particular pieces of code which could be tweaked.

[mdmackillop]

Hi Ismael
Your code is inserting 4 columns, A-D, You are then pasting in to B a function which is looking for No, Data or a Value in column C, which is still blank. You then copy and paste special the blank values in column B, copy them from B to C and then delete them from B. I think you need to clarify what you are trying to achieve here.
Also, can you explain the value in the lookup function LOOKUP(9.99999999999999E+307,R1C4:RC[2])
Regards
MD

[Ismael]

Hi,

In fact my code is very confuse, I will try to eliminate some stuffs. End then I will tell something.

Regarding the value of lookup function, I don't now how it works, because this is a person from Mrexcel that post to one of my doubts.

I have post this formula on this link, is the last answer.

http://www.vbaexpress.com/forum/showthread.php?t=4438


regards,

Ismael

[mdmackillop]

From the help file
LOOKUP(lookup_value,array)

Lookup_value is a value that LOOKUP searches for in an array. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.



If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.


In your case, your Value is a very large number 10^306, and Lookup returns the nearest value in the range D$1:D row

Try ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(RC[1]),R4C4,"""")", amending R4C4 as required to the row/column of your actual data

[Ismael]

Hi,

Thanks, for the effort one try to help me.

But let see one this link

http://www.vbaexpress.com/forum/showthread.php?t=4438

I say that from time to time the values that are on column D they change. in the example that I send you, I put just one value because the file was to large end I delete a big part of it.

but I will make another example on this formula, because I now that this lookup is slow down my machine a lot....:dunno


In the attach I will put the lookup function that I have used one the macro end I will put your formula end you will see the difference (the formula are on the green cells).

I hope that you have a solution for this lookup, because otherwise my computer will stay very slow.

Thanks and regards,

Ismael

[Bob Phillips]

Ismael,

I don't think you will get much quicket than using BigNUmber. I have just done a comparison, building a nice array formula - it was 20 times as slow.

I also checked the benchmarks on this site http://www.xldynamic.com/source/xld.LastValue.html, and that came to the same conclusion, LOOKUP(Bignumber) is the quickest.

[Ismael]

Hi,

Ok I will keep using the lookup.

Thanks for the help

Regards,

Ismael