以下代码返回错误1004

Question

我想在代码中使用工作表的名称来选择一个范围。我有37张，我不想写代码37次。

Sub relatorio() 
    Dim ws As Worksheet, GI As Integer, J As Integer 
    J = 0 
    For Each ws In ActiveWorkbook.Worksheets 
     J = J + 1 
     If ws.Name = "Brasil" Then 
      i = (8 * 0) + 4 And GI = (13 * 0) + 271 
     End If 
     If ws.Name <> "Dados" Then 

      'the error occur in the next line. 
      Worksheets(ws.Name).Shapes.Range(Array("TRI")).Select 
      Selection.Formula = "=Dados!a2" 
      Selection.ShapeRange.TextFrame2.TextRange.Font.Name = "Calibri" 
      Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 9 

     End If 
    Next 

End Sub 

我不知道该怎么办

Answer 1

Worksheets(ws.Name).可以ws.更换。
留给你：

ws.Shapes.Range(Array("TRI")).Select 

你有一个名为“TRI”，它是示数出在工作表上的形状，或者是该板本身隐藏？

，以避免错误床单被隐藏，并避免选择每个形状，我建议这样的：

with ws.Shapes.Range(Array("TRI")).Select 
    .Formula = "=Dados!a2" 
    .ShapeRange.TextFrame2.TextRange.Font.Name = "Calibri" 
    .ShapeRange.TextFrame2.TextRange.Font.Size = 9 
end with 

Answer 2

有几件事情在你的颂歌回事。

首先，该行：

i = (8 * 0) + 4 And GI = (13 * 0) + 271 

...最有可能的是不是你做何意。它现在就像一个孤立的布尔标准。如果您尝试将值分配给i和GI，最好将它放在两行上。

i = (8 * 0) + 4 
GI = (13 * 0) + 271 

接下来的事情会是你使用ws。从Worksheets collection到For Each...Next Statement将其分配到Worksheet object后，您可以直接使用它。您不需要使用它的名称作为参考点。

Sub relatorio() 
    Dim ws As Worksheet, GI As Integer, J As Integer 
    J = 0 
    For Each ws In ActiveWorkbook.Worksheets 
     with ws 
      J = J + 1 
      If .Name = "Brasil" Then 
       i = (8 * 0) + 4  'not at all clear what you are trying to do here 
       GI = (13 * 0) + 271 'not at all clear what you are trying to do here 
      End If 
      If .Name <> "Dados" Then 

       'the error occur in the next line. 
       'the recorded code for selecting a shape object goes overboard; this is all you should require 
       with .Shapes("TRI") 
        .Formula = "=Dados!a2" 
        .ShapeRange.TextFrame2.TextRange.Font.Name = "Calibri" 
        .ShapeRange.TextFrame2.TextRange.Font.Size = 9 
       end with 
      End If 
     end with 
    Next ws 

End Sub