VBA Insert formula not working.

  • Hi,

    I am trying to inset a formula with VBA, and I am getting the following result.


    The formula I want is =TRANSPOSE('MOH Chart'!M2:APF7)

    The result I am getting is [email protected](@'MOH Chart'!M2:APF7)


    My code is below.


    Can anyone please help?


    Code
     WS1.Range("A2").Formula = "=TRANSPOSE('MOH Chart'!M2:APF7)"
  • Hi,


    What is precisely : MOH Char ???


    Have you looked into how to use VBA to Transpose of two-dimensional Array ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

  • By the way ...


    Have you solved your previous question ...???



    The question you asked 3 months ago .... back in November ....

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • OK ...


    And you Array ... if I am not mistaken ... is 6 Rows and 1'086 Columns

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • You could test

    Code
    Sub TestBah73()
      Sheet1.Range("A1:F1086").Value = Application.WorksheetFunction.Transpose(Sheets("MOH Chart").Range("M2:APF7"))
    End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks Carim,


    That code works but it only transposes the values and doesn't insert the formula into the cell.


    This is a problem because when the main sheet data is updated the transposed data don't update with it.

    Edited once, last by Bah73 ().

  • Quite obviously, I do not know your workbook structure ... nor do I know the update frequency you would need ...


    Many different possibilities exist for a constant update, such as Run the macro, rely on an event macro, use a simple Offset() formula, etc ....


    Attaching a sample file with just a few fake records would greatly help to visualize your constraints ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • All good Carim I figured out how to use the code you supplied.


    The transposed data is used to create a pivot table for a chart.


    I created button with a refresh all code including your code and so every time I refresh the chart the transpose data updates.


  • Glad you have managed to solve your problem


    Thanks for your Thanks AND for the Like :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • For the final touch I added this to the sheet module where the chart is located.


    Now every time I open the sheet to view the chart the transpose data and pivot table data update automatically.


    Does anyone know why I was getting the @ symbol in my formula if I was trying to insert it with VBA?


    See first post.


    Code
    Private Sub Worksheet_Activate()
    Call Refresh_Chart
    End Sub
  • Does anyone know why I was getting the @ symbol in my formula if I was trying to insert it with VBA?


    An implicit intersection operator represented by @ was introduced to support the New Dynamic Arrays and manage the Spill effect


    see link : https://support.microsoft.com/…01-4450-a24e-c1c999be2b34

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!