Automate tasks in a macro or VBA script
I am a complete newbie. I am sorry that this question and explanation is so long, but I do not know how else to try to get an answer.
I have tried and read loads and gone nowhere fast.
I have managed to create a macro where I use an if formula to compare two columns, but my problem is the following, I have more than 1000 rows to work on, I basically compare column A to Column C looging for the higher number.
The formula I use is below. =IF(A5=0,"Preview",IF(A5=C5,"Good",IF(A5>C5,"Content",IF(A5
| Attachment | Size |
|---|---|
| compare_demo_start.xls | 47.5 KB |
| compare_demo_final.xls | 50 KB |
| compare_demo_start-Nick.xls | 73 KB |
| getfile_macro.txt | 7.6 KB |
| compare_catalog_error.jpg | 85.33 KB |
| Compare_Catalog.txt | 7.67 KB |
Macro help
Hi.. we have this:
For i = 3 To Cells(65000, 1).End(xlUp).Row
...the problem is that Cells(65000, 1).End(xlUp).Row is calculated at the beginning. When we add rows, it's not being recalculated.
An easy way around this is to change the 1st line to:
For i = 3 To 650000 (Or any number that you are sure it won't get past... )
Rgds Nick
Macro Help,
Nick,
Thanks for the help, that solved my problem, but I would like to understand how and why.
The confusing thing is that the error occured on line 1278 and that is way off 65000. So ho is the restriction imposed if I have not reached line 65000.
What does the 65000 decalre, 65000 characters or 65000 rows ?
Thansk again for your great help.
Regards
Lawrence
explanation
hey..
the line: for i = X to Y loops through i from the starting point: X to the end point Y.
In our code, we set an upper limit for Y, and that upper limit was the amount of rows the data contained AT THE BEGINNING of the run.
As we run the code, rows are inserted, so the total amount of rows that we need to loop through increase each time a row is inserted.
The main problem here is that at the beginning of the run, we don't know how many rows are going to be inserted, so by setting Y = 65000 all we're saying is: loop through a big enough range to guarantee we've included all the data.
Rgds
Nick
Sorry for misleading ...
Nick,
Sorry for misleading you, but I have changed the 65000 to 650000 and this has not made a difference.
The problem occurs on the second worksheet, but always at the same spot. I will try to increase the number to 990000 and see what the effect is, but I do not hold my hopes too high.
Thanks
Lawrence