VBA一次粘贴一 block 单元格

Question

我目前正在尝试编写如下所示的 VBA 代码。但是，当我从“another_ws”中提取值时，需要永远更新每个值。有没有更快的方法呢？代码需要几个小时才能运行。

Dim another_ws as worksheet
Set another_ws = wb.sheets("sheet1") 

For row = 1 To 500
    For column = 1 To 500
        ws.cells(row, column).value = _ 
        ws.cells(row, column).value + another_ws.cells(row, column).value
        'another_ws comes from another workbook
    Next column
Next row

Answer 1

几种不同的方法:

工作表。评估:

Dim another_ws As Worksheet
Set another_ws = wb.Sheets("sheet1")
ws.Range("A1").Resize(500, 500).Value = ws.EVALUATE("INDEX(" & ws.Range("A1").Resize(500, 500).Address(1, 1) & _
    " + " & another_ws.Range("A1").Resize(500, 500).Address(1, 1, xlA1, True) & ",)")

使用数组:

Dim another_ws As Worksheet
Set another_ws = wb.Sheets("sheet1")    
Dim oarr() As Variant
Dim tarr() As Variant

oarr = ws.Range("A1").Resize(500, 500).Value
tarr = another_ws.Range("A1").Resize(500, 500).Value

For i = 1 To 500
    For j = 1 To 500
        oarr(i, j) = oarr(i, j) + tarr(i, j)
    Next j
Next i

ws.Range("A1").Resize(500, 500).Value = oarr

或者正如@Gary 的学生所说，PasteSpecial 添加:

Dim another_ws As Worksheet
Set another_ws = wb.Sheets("sheet1")

another_ws.Range("A1").Resize(500, 500).Copy
ws.Range("A1").Resize(500, 500).PasteSpecial xlPasteAll, xlPasteSpecialOperationAdd