我怎样才能使公式的一部分变化？

I am trying to make just a part of the code change while the worksheet change, but it does not work. Changing from one worksheet to other is OK, but the changing integer do not work in the code. I create the J Integer to grow in one unit as the worksheet change. Therefore, the graph and the shapes are based on another Sheet (Dados) which has the data in different lines to provide the information to each worksheet.

我试图在工作表更改时只更改代码的一部分,但它不起作用。从一个工作表更改为其他工作表是可以的,但更改的整数在代码中不起作用。随着工作表的改变,我创建了J Integer以在一个单元中增长。因此,图形和形状基于另一个Sheet(Dados),其具有不同行中的数据以向每个工作表提供信息。

Sub relatorio()
Dim ws As Worksheet, GI As Integer, GF As Integer, J As Integer
J = 0
For Each ws In ActiveWorkbook.Worksheets
    With ws
        J = J + 1
        If .Name = "Brasil" Then
            i = (8 * J) + 4   'the 8 is the amount of lines below I need to catch and the 4 is because the data starts on line 4. 
            GI = (13 * J) + 271
            Gf = (13 * J) + 283    
        End If
        If .Name <> "Dados" Then
            With .Shapes("TRI")
                .Formula = "=Dados!a2"
                .ShapeRange.TextFrame2.TextRange.Font.Name = "Calibri"
                .ShapeRange.TextFrame2.TextRange.Font.Size = 9
            End With

It does not work from the following step on. Would you please help me to solve this problem?

它不适用于以下步骤。你能帮我解决这个问题吗?

        With .Shapes("PIT")
            .Formula = "=Dados!E(i)"
            .ShapeRange.TextFrame2.TextRange.Font.Name = "Calibri"
            .ShapeRange.TextFrame2.TextRange.Font.Size = 9
        End With
        With .ChartObjects("Gráfico 8")
            ActiveChart.PlotArea.Select
            ActiveChart.FullSeriesCollection(1).Values = "=Dados!$e$(GI):$e$(GF)"
            ActiveChart.FullSeriesCollection(1).XValues = "=Dados!$b$(GI):$c$(GF)"
            ActiveChart.SetElement (msoElementDataLabelTop)
            ActiveChart.FullSeriesCollection(1).DataLabels.Select
            Selection.NumberFormat = "#.##0,0"
        End with
    End if
Next
end sub

2 个解决方案

#1

I am thinking the line that is causing the problem is .Formula `= "=Dados!E(i)" , is I intended to be a variable ? as it is written it is not. I know you assign it in the previous sheet, but for this sheet it is not.

我在想导致问题的那一行是.Formula` =“= Dados!E(i)”,我打算成为一个变量吗?因为它写的不是。我知道你在上一张纸上分配它,但是对于这张纸,它不是。

Perhaps try: .Formula = "=Dados!E(" & i & ")"

也许试试:.Formula =“= Dados!E(”&i&“)”

#2

First off I would get the variable declarations in line with the scope of a modern worksheet. It may not matter now but it is a good practise to get into.

首先,我会得到符合现代工作表范围的变量声明。现在可能没关系,但进入是一个很好的做法。

Dim ws As Worksheet, GI As Long, GF As Long, J As Long

Next, the construction of cell ranges from string constants and numerical variables does not look correct. You are trying to create something like =Dados!E99 not =Dados!E(i) when i has the value of 99.

接下来,从字符串常量和数值变量构造单元格范围看起来不正确。你正试图创造像= Dados这样的东西!E99 not = Dados!E(i)当我的值为99时。

    With .Shapes("PIT")
        .Formula = "=Dados!E" & i
        ...
    End With
    With .ChartObjects("Gráfico 8")
        ...
        ActiveChart.FullSeriesCollection(1).Values = "=Dados!$e$" & GI & ":$e$" & GF
        ActiveChart.FullSeriesCollection(1).XValues = "=Dados!$b$" & GI & ":$c$" & GF
        ...
    End with

#1