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 =@TRANSPOSE(@'MOH Chart'!M2:APF7)
My code is below.
Can anyone please help?
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 =@TRANSPOSE(@'MOH Chart'!M2:APF7)
My code is below.
Can anyone please help?
Hi,
What is precisely : MOH Char ???
Have you looked into how to use VBA to Transpose of two-dimensional Array ???
By the way ...
Have you solved your previous question ...???
The question you asked 3 months ago .... back in November ....
Thanks Carim,
MOH Chart is the sheet name where the data is.
OK ...
And you Array ... if I am not mistaken ... is 6 Rows and 1'086 Columns
Yes, that sounds right.
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.
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 ...
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.
Sub Refresh_Chart()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim WS1 As Worksheet
Set WS1 = Worksheets("Labour Chart")
WS1.Range("A2:F1092").Value = WorksheetFunction.Transpose(Sheets("MOH Chart").Range("M2:APK7"))
ThisWorkbook.RefreshAll
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Display More
Glad you have managed to solve your problem
Thanks for your Thanks AND for the Like
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.
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/en-us/office/i…4e-c1c999be2b34
Don’t have an account yet? Register yourself now and be a part of our community!