无法粘贴 - Excel VBA
新的VBA试图弄清楚。使用代码尝试从各种文件自动复制到具有公式的文件,计算并再返回。 当我尝试粘贴时,遇到了'运行时错误'1004',范围类的pastespecial方法失败'消息。该消息仅在使用变量声明第一个单元格时出现,以便复制一系列值。 当即时通讯使用直接单元格描述一切工作正常。 我还使用自定义函数来获取给定字段名称的列字母。无法粘贴 - Excel VBA
Function ActiveColumnName(fieldname As String, fieldnames_line As Integer) As String
Range("A" & fieldnames_line & ":AB" & fieldnames_line).NumberFormat = "@"
Cells.find(What:=fieldname, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveColumnNumber = ActiveCell.Column
Dim m As Integer
Dim ActiveColumnName As String
ActiveColumnName = ""
Do While (ActiveColumnNumber > 0)
m = (ActiveColumnNumber - 1) Mod 26
ActiveColumnName = Chr(65 + m) + ActiveColumnName
ActiveColumnNumber = Int((ActiveColumnNumber - m)/26)
Loop
End Function
sub main()
Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer
firstrow_data_main = 16
firstrow_fieldnames_main = 15
Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & firstrow_data_main, Range(ActiveColumnName("ÄÅÔÅ", firstrow_fieldnames_main) & Rows.Count).End(xlUp).Offset(-1)).Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open help_file '"help_file" is any given .xls path with formulas
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer
firstrow_data_help = 7
firstrow_fieldnames_help = 4
'NOW WHEN I USE THIS, DOESNT WORK:
-> Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & firstrow_data_help).Select
'WHEN I USE THIS, WORKS FINE:
-> Range("L7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
当它不工作时,它打开.xls和它的确选择了理想的细胞,但没有脑袋。我知道这与剪贴板有关,但无法弄清楚。有什么建议么?
- 通过直接引用单元格删除所有的选择和激活,见HERE获取更多信息。
- 查看Cells()而不是Range,并避免将列号转换为字母的整个需求,因为Cells()使用数字。
- 当值是唯一需要的值时避免剪贴板,并将值简单地分配给新单元格(这将要求两个范围的大小相同,因此请使用Resize())
- 总是表示范围,它会减少错误。
重构的代码
Sub main()
Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer
Dim rng As Range
Dim tWb As Workbook
Dim ws As Worksheet
Dim tWs As Worksheet
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer
Set ws = ThisWorkbook.ActiveSheet
Set tWb = Workbooks.Open(help_file)
Set tWs = tWb.ActiveSheet
firstrow_data_main = 16
firstrow_fieldnames_main = 15
firstrow_data_help = 7
firstrow_fieldnames_help = 4
With ws
Set rng = .Range(.Cells(firstrow_data_main, firstrow_fieldnames_main), .Cells(.Rows.Count, firstrow_fieldnames_main).End(xlUp).Offset(-1))
tWs.Cells(firstrow_data_help, firstrow_fieldnames_help).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End With
End Sub
我认为这个问题可能是在这里:
ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main)
这意味着ActiveColumnName
是n x (1 to 2)
维度的矩阵。如果你想连接一个名字给一个变量,你必须使用(例如):
"YourStringHere" & YourVariableHere & "AnotherString"
而你的情况应该是:
ActiveColumnName("<FIELDNAME>" & firstrow_fieldnames_main)
所以,如果我理解正确的(<FIELDNAME>
有点晦涩),整个命令应该是:
Range(ActiveColumnName("<FIELDNAME>" & firstrow_fieldnames_help) & "," & firstrow_data_help).Select
首先,运行之前,你应该编译VBA。在VBA编译器捕获这种蝙蝠:Dim ActiveColumnName As String
是不必要的,因为你分配ActiveColumnName作为字符串,当你在第1行
定义的函数可以使用很多活性细胞和细胞选择参考。这被称为导致运行时错误。看到这篇文章:"How to avoid using Select in Excel Vba Macros"。
我怀疑字段名不在你认为它应该在你的help_file中的位置,即它不在第4行。这将意味着代码不知道在哪里粘贴数据。通常,调试的最佳方式是将代码分成尽可能最小的动作,以查看导致错误的原因(请参阅SpreadSheet Guru Strategies)。你可以运行下面的代码来看看输出是什么?
Function ActiveColumnName(fieldname As String, fieldnames_line As Integer) As String
Range("A" & fieldnames_line & ":AB" & fieldnames_line).NumberFormat = "@"
Cells.Find(What:=fieldname, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
ActiveColumnNumber = ActiveCell.Column
Dim m As Integer
ActiveColumnName = ""
Do While (ActiveColumnNumber > 0)
m = (ActiveColumnNumber - 1) Mod 26
ActiveColumnName = Chr(65 + m) + ActiveColumnName
ActiveColumnNumber = Int((ActiveColumnNumber - m)/26)
Loop
End Function
Sub main()
Workbooks.Open "help_file" '"help_file" is any given .xls path with formulas
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer
firstrow_data_help = 7
firstrow_fieldnames_help = 4
MessageBox = ActiveColumnName("FIELDNAME", firstrow_fieldnames_help) & firstrow_data_help
End Sub
谢谢大家的回复!我尝试了所有的建议,但一路上遇到了各种问题。不过,您的所有建议都有助于我对这个问题发展出不同的看法。我最终得到的解决方案源于你的建议,放弃“.select”作为参考方式,并使用“rng”变量,当然也可以去掉双引用“ActiveColumnName”。我知道我有一个很长的路要走,但目前这个东西的作品!!谢谢!
子主()
Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer
firstrow_data_main = 16
firstrow_fieldnames_main = 15
firstrow_data_help = 7
firstrow_fieldnames_help = 4
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & firstrow_data_main, Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & Rows.Count).End(xlUp).Offset(-1))
cells_selected = rng1.Rows.Count
Workbooks.Open <help_file>
Set rng2 = Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & firstrow_data_help, Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & cells_selected + firstrow_data_help - 1))
rng1.Copy rng2
结束子
1.除去所有的选择和激活通过直接参照的细胞,见[HERE](http://*.com/questions/ 10714251/how-to-avoid-using-select-in-excel-vba-macros)2.查看“Cells()”而不是Range,避免将列号转换为字母的整个需求, )'使用数字。 3.当值是唯一需要的时候避免剪贴板,并简单地将值赋给新单元格(这将要求两个范围的大小相同,因此请使用Resize())4.始终表示范围的父表单它会减少错误。 –