excel - 需要帮助来搜索字符串并将数据覆盖到同一位置

Question

我有这段代码可以将数据写入下一个空列，并且当记录不存在时它可以完美运行。但是，我添加了代码来搜索记录（MyVal），如果找到，询问我们是否应该覆盖。当它询问用户是否说不时，它会退出子程序（这是正确的行为），但如果记录存在，我希望它将数据写入同一位置。我很确定我在错误的位置有“Else”，我不知道要编写什么代码来告诉它将数据写入与找到的记录相同的位置。

Sub Copy_To_Borrower_DBase()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim myVal As String
Dim sourceRng As Range
Dim lCol As Long
MyNote = Sheets("Main").Range("F5").Value & _
         " already exists, do you want to overwrite?"
myVal = Sheets("Main").Range("F5").Value ' dropdown list

Set sourceRng = Worksheets("Borrower Database").Range("5:5").Find( _
       What:=myVal, LookAt:=xlWhole) 'locate column where to copy from

Set ws1 = ThisWorkbook.Sheets("Main")
Set ws2 = ThisWorkbook.Sheets("Borrower Database")

If Not sourceRng Is Nothing Then
    Answer = MsgBox(MyNote, vbCritical + vbYesNo, "Overwrite??")
    If Answer = vbNo Then
        Exit Sub
    Else
    End If
Else
With ws2
Application.EnableEvents = False
lCol = .Cells(5, .Columns.Count).End(xlToLeft).Offset(, 1).Column
    .Cells(5, lCol).Value = ws1.Range("F5").Value 'Borrower Name
    .Range(.Cells(6, lCol), .Cells(8, lCol)).Value = 
ws1.Range("G6:G8").Value 'Income, Credit Pmt and Car Pmt
    .Range(.Cells(9, lCol), .Cells(10, lCol)).Value = 
ws1.Range("G11:G12").Value 'Borrower Name
    .Cells(11, lCol).Value = ws1.Range("G15").Value 'Reserves
    .Cells(12, lCol).Value = ws1.Range("D15").Value 'Credit Score
    .Cells(13, lCol).Value = ws1.Range("D14").Value 'Rate
    .Cells(14, lCol).Value = ws1.Range("C14").Value 'Discount Point
Application.EnableEvents = True
End With
End If
End Sub

标签： excelvba