Forum Discussion

Deleted's avatar
Deleted
Apr 11, 2018

Macro has to be run twice

I'm working on an excel file that was designed by someone else. I am only new to VBA. I run the macros previously created to import sheets and data. I then run my simple macro to update three cells that are "#REF" after the first set of macros are run. My new macro fixes the #REF, but information that was imported from the old macros disappears and they need to be run again. 

 

I understand why I have to refresh the cells, but cannot find a solution as to why the other information is disappearing and needs to be imported again.

 

Any help would be greatly appreciated. Thanks in advance.

Macros & VBA
VBA
  • Tomasz Kocur's avatar
    Tomasz Kocur
    Brass Contributor
    Apr 11, 2018

    Hi Darren 

    it's hard to say without seeing the original file

    I can assume what happened to me once, it may need more time to access the hard drive (file) so you will have to put a delay in the code

     

    Application.Wait(Now + TimeValue("0:00:10"))

     

    try and let me know is it working

    • Deleted's avatar
      Deleted
      Apr 11, 2018

      Thanks for the reply.

      That doesn't seem to solve the problem. 

      I have two buttons on the macro, One runs all of the previously created macros and fills the excel with data from different sheets that it imports, leaving only three cells with #REF. 

      When I click the second button, It fills the three cells but changes cells previously containing data to #REF.

      I click the first button again and now everything has the correct data and I don't have any cells containing #REF. 

      I am unable to share the workbook.

       

      I hope that my explanation in this reply helps you to understand my problem. Thanks again for your help.

       

      • Tomasz Kocur's avatar
        Tomasz Kocur
        Brass Contributor
        Apr 11, 2018
        https://support.office.com/en-ie/article/how-to-correct-a-ref-error-822c8e46-e610-4d02-bf29-ec4b8c5ff4be

        Go through macro step by step (F8) and observe what and when is happening

        Macro Issues
        If a macro enters a function on the worksheet that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1. Check the function to see if an argument refers to a cell or range of cells that is not valid. This may require editing the macro in the Visual Basic Editor (VBE) to take that situation into account.

Resources