根据单元格中的行号值粘贴到行

Question

我正在尝试编写一个宏，该宏从表单(Contractor Entry form，range"U5:AT5"(复制数据并将其粘贴到数据库(Contractor database(。

当一条记录被编辑时，它请求雇员ID#并在数据库中找到该行，并将该行号参考临时粘贴到单元格"承包商输入表"中；L1"；。

然后我需要将复制的数据粘贴到单元格"中引用的行号(-1(上的数据库中；L1"；。如果在"0"中没有值；L1"；这意味着它是一个新条目，然后应该粘贴到最后一行，而不是粘贴到前一个记录行上。

请帮帮我。我的代码在这里--

Sub ContractorEntry

Range("U5:AT5").Copy
Sheets("CONTRACTOR_DATABASE").Select

Dim R As Integer
R = Worksheets("CONTRACTOR ENTRY").Range("L1").value
'note-- if there is a value in CONTRACTOR ENTRY L1>0 then 
'  (it represents a row number --- paste value to that row -1 onto 
'  Contractor Database sheet.
If Worksheets("CONTRACTOR ENTRY").Range("L1") > 0 Then
Sheets("CONTRACTOR_DATABASE").Cells (R -1, 1)
Selection.PasteSpecial
End If

Else
'if there is no value in cell L1 then the following to just paste to the next blank row
lMaxRows = Cells(Rows.Count, "A").End(xlUpSelection.PasteSpecial.Row

Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks :=False, Transpose:=False

'This returns to the contractor entry form and clears contents
Sheets("CONTRACTOR ENTRY").Select
Range("D3:M1").Select
Selection.ClearContents

'Should go back to Contractor Entry Form for Name and a new entry in cell D3
Range("D3").Select

End Sub 

Answer 1

几乎不需要使用Select/Activate。有关如何改进代码的指导方针，请参阅此处：如何避免在Excel VBA 中使用Select

像这样的东西应该起作用：

Sub ContractorEntry()
Dim rw, wsInput As Worksheet, wsDB As Worksheet

'use worksheet varaibles for easier maintenance
Set wsInput = ThisWorkbook.Worksheets("CONTRACTOR ENTRY")
Set wsDB = ThisWorkbook.Worksheets("CONTRACTOR_DATABASE")

rw = wsInput.Range("L1").Value - 1

'if row not present then get next empty row
If rw < 1 Then rw = wsDB.Cells(Rows.Count, "A").End(xlUp).Row + 1

'copy over values directly (no copy/paste)
With wsInput.Range("U5:AT5")
wsDB.Cells(rw, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

With wsInput
.Activate
.Range("D3:M1").ClearContents
.Range("D3").Select
End With
End Sub