none
Ref changes due to VBA RRS feed

  • Question

  • I have a workbook that pulls data from a en access source, after that a VBA script changes the data in different tables.

    I have one sheet that takes data by referencing the data that have been altered by the VBA. 

    For some reason the sheet that takes the data by reference get altered when I run the VBA script.

    Any ideas why this is happening?


    Regards Carl-Johan Larsson

    Monday, May 1, 2017 2:34 PM

All replies

  • Hi Carl,

    Thanks for visiting our forum.

    Then here we mainly focus on general questions about Excel client. Since your issue is more related to VBA script, we'll move your thread to the following dedicated MSDN forum for Excel for better response:

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Best regards,
    Yuki Sun


    Please remember to mark the replies as answers if they helped.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, May 2, 2017 3:07 AM
  • Hello,

    What has been changed by the code? The data in the sheet or in the access table?

    What code do you use? I suggest you share some screenshots with sample data to clarify.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 2, 2017 6:35 AM
    Moderator
  • Background:

    I have a very complex Access database that I need to share with coworkers and get updates for them.

    Since we can not share the Access database I import the tables into Excel. Since the tables are linked, I need to run some VBA code in order to make the data readable to the coworker.

    This is what is happening.

    First several tables gets updated from Access, after that i run some code to be able to present the data in a presentable way.

    I then have one sheet that contains the data that the coworker may or may not have edited, the data is pulled from a sheet that the coworker can edit.

    This is what the coworker sees:

    Data from that sheet is then pulled into another sheet by reference.

    Since for example Country is stored in the Access database as a ref, this is being taken by Vlookup into the sheet that will later be used to send the data back to the Access DB, that looks like this:

    

    But when I add data in the Access database and update the excel sheet, this happends:

    Sometimes only for all the rows and sometimes for all rows under the "new" records.

    The VBA code doesnt touch the sheet that contains the references that gets broken.

    Here is the VBA code:

    Private Sub PrepareCompanyData()

    'Copy the data
    Sheets("CompanyData").Range("A2:A2000").Value = Sheets("CompanyTable").Range("B2:B2000").Value
    Sheets("CompanyData").Range("B2:B2000").Value = Sheets("CompanyTable").Range("A2:A2000").Value
    Sheets("CompanyData").Range("C2:E2000").Value = Sheets("CompanyTable").Range("C2:E2000").Value

    'Set RowHeight
    Sheets("CompanyData").Rows("1:2000").RowHeight = 15

    'For the worksheet

    'Copy the data
    Sheets("Companies").Range("A2:A2000").Value = Sheets("CompanyData").Range("A2:A2000").Value
    Sheets("Companies").Range("B2:D2000").Value = Sheets("CompanyData").Range("D2:F2000").Value


    'Hides the Columns not needed and fits the column to the correct size
    Sheets("Companies").Columns("A:XFD").EntireColumn.AutoFit
    Sheets("Companies").Columns("E:XFD").EntireColumn.Hidden = True

    'Set RowHeight
    Sheets("Companies").Rows("1:2000").RowHeight = 15

    ' Hide Unused Rows
    Dim dblRowNumber As Double
    dblRowNumber = Val(Sheets("CompanyTable").ListObjects("Table_CompanyTable").Range.Rows.Count) + 1
    Sheets("Companies").Rows(dblRowNumber & ":1048576").EntireRow.Hidden = True
    End Sub

    Is there any way of "locking" the cells in the last sheet so no matter what happens the formula doesn'tchange?

     


    Regards Carl-Johan Larsson

    Tuesday, May 2, 2017 9:15 AM
  • Hello,

    Could you please share detail steps how I can reproduce your issue?

    I understand you have three sheets. In one sheet, there a linked table. In the other two sheets, it seems you are copying data from the sheet which has the linked table. But at the same time, you are using formula to reference data between the two sheets.

    In which sheet do you use the vlookup formula and which sheet does the formula to look up?

    >>Is there any way of "locking" the cells in the last sheet so no matter what happens the formula doesn'tchange?

    In your code, you are copying data. We could not let cell accept copied value and keep formula as the same time.

    Besides, The #REF! error shows when a formula refers to a cell that’s not valid . This happens most often when cells that were referenced by formulas get deleted, or pasted over.

    So I think you get the error because you are using VBA to paste data to one cell, which causes the reference in the formulas deleted or pasted over.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, May 3, 2017 7:26 AM
    Moderator