0

I have the following very weird scenario :

I have a template of an excel file and I create a duplicate of it (ctrl+c, ctrl+v).

I have another excel file, where a macro checks if some cells are empty in that duplicated file : 

If ActiveCell.Offset(i - 1, 0) = "" Then

The cells are empty but this test will fail.

BUT :

If I open the duplicated file, go on the relevant spreadsheet (and not an other!), without no modification but just saving the file and closing it, I can then launch again the macro and it will work ! So the cell is empty, but it's like if Excel needs to initialize the spreadsheet first.

How can I avoid opening the file and saving it ? Because it can happen that another person will use the duplicated file, open it, change something on a different spreadsheet, save it, and close it. My macros must still result in saying that the cells are empty !

Do you have any idea what's going on ? Thank you in advance !

Stephane
  • 11
  • 5
  • 6
    I'm going to go out on a very short limb and suggest the problem is your use of ActiveCell and Select. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020) –  Jun 05 '18 at 20:07
  • "...this test will fail." - How does it fail? Does it throw an error, does it return the incorrect information, etc.? You're probably also needing to declare the Workbook you're using in your macro (see @Jeeped's link) – BruceWayne Jun 05 '18 at 20:15
  • Thank you for your answers. I have read your link and tried a few things, but as I'm a beginner, I cannot understand what I should do. @BruceWayne : it's a IF statement. I'm checking (example) if Cell C4 is not empty. So IF C4 <> "", show true (not empty), or else show false. As long as I don't open each separate book, go to the specific sheet, save the sheet (with no modification), close the file, and rerun my macro, my test will be false (not empty) while it should be empty. Once I have opened the file and closed it, the same test will work. Help :) – Stephane Jun 16 '18 at 16:04

0 Answers0