如何在Vlookup VBA中使用lastRow

Question

我目前有三个工作表，分别是Tracker、SIM Data和Site Data。在SIM Data工作表中，我使用了VBA中的G3、H3和I3列的Vlookup函数，以便从工作表Site Data中找到工作正常的站点类型、电子邮件和站点。一旦数据被组织好，我将把信息传输到Tracker工作表上，它也可以很好地工作。

我的问题是，一旦信息传输到Tracker工作表上，如果我想添加任何额外的数据，它将替换顶部数据，而不是从跟踪器上已经存在的底部数据开始。我知道lastRow可以解决这个问题，但是我不确定如何输入它(或者我已经拥有的代码在哪里)。任何帮助都将不胜感激。

'SIM Data Macro
Sub SIM_Macro()
Dim lr As Long
Dim lr1 As Long
Dim lr2 As Long
 'Dim Gcell As Range
 'Dim lLastRow As Long
 'Dim LResult As String

lr = Sheets("SIM Data").Range("A" & Rows.Count).End(xlUp).Row

 'Select ranges and delete

    Range("A:A,H:J").Delete

 'Type in values in G3 and H3

    Range("$G$3").Value = "Site Type"
    Range("$H$3").Value = "Email"
    Range("$I$3").Value = "Site"

    Cells.EntireColumn.AutoFit

    Range("G4").Formula = "=VLOOKUP(LEFT(RC[-3],4),'Site Data '!C[-6]:C[4],2,FALSE)"

    Range("G4").AutoFill Destination:=Range("G4:G" & lr)

'VlookUp for email tab

    Range("H4").Formula = "=VLOOKUP(LEFT(RC[-4],4),'Site Data '!C[-7]:C[4],10,FALSE)"

    Range("H4").AutoFill Destination:=Range("H4:H" & lr)

'LEFT Formula for Site

    Range("I4").Formula = "=LEFT(RC[-5],4)"

    Range("I4").AutoFill Destination:=Range("I4:I" & lr)

    Cells.EntireColumn.AutoFit

    Range("G4").Sort key1:=Range("G4:G" & lr), _
    order1:=xlDescending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom


' Keep the formulas on the tracker tab untouched.

    'Site Formula
    Sheets("Tracker").Activate


  Sheets("Tracker").Range("A5").Formula = "=INDEX('SIM Data'!A4:I100,1,9)"
  Range("A5").AutoFill Destination:=Range("A5:A" & lr + 1)


'SEV2 Formaul on Tracker sheet

    Sheets("Tracker").Range("B5").Formula = "=INDEX('SIM Data'!A4:H100,1,2)"
    Range("B5").AutoFill Destination:=Range("B5:B" & lr + 1)

' Formula for Site Type in Tracker Tab

    Sheets("Tracker").Range("F5").Formula = "=INDEX('SIM Data'!A4:H100,1,7)"
    Range("F5").AutoFill Destination:=Range("F5:F" & lr + 1)

' Formula for SIM in Tracker Tab

    Sheets("Tracker").Range("G5").Formula = "=INDEX('SIM Data'!A4:H100,1,1)"
    Range("G5").AutoFill Destination:=Range("G5:G" & lr + 1)

'Formula for Email

    Sheets("Tracker").Range("H5").Formula = "=INDEX('SIM Data'!A4:H100,1,8)"
    Range("H5").AutoFill Destination:=Range("H5:H" & lr + 1)

'Add hyperlinks to G (Sim) Column

lr1 = Sheets("Tracker").Range("G" & Rows.Count).End(xlUp).Row

    Sheets("Tracker").Range("G5:G" & lr1).copy
    Sheets("Tracker").Range("G5").PasteSpecial xlPasteValues

    Range("G5").Select
    For i = 5 To lr
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Offset(5, 7).Value
        ActiveCell.Offset(6, 7).Select
    Next

'Add hyperlinks to H (Email) Column

lr2 = Sheets("Tracker").Range("H" & Rows.Count).End(xlUp).Row

    Sheets("Tracker").Range("H5:H" & lr2).copy
    Sheets("Tracker").Range("H5").PasteSpecial xlPasteValues

    Range("H5").Select
    For i = 5 To lr
        ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:=ActiveCell.Offset(5, 7).Value
        ActiveCell.Offset(6, 7).Select
    Next

    Application.CutCopyMode = False

    Range("A4").Select

End Sub

Answer 1

我最终弄明白了。

Dim r As范围

Set r=Worksheets(“SIM卡数据”).Range(“A4:A”& Application.Match("*"，Worksheets(“SIM卡数据”).Range(“G:G”)，-1)) Set r2 =Worksheets(“跟踪器”).Range(“G”& Rows.Count).End(xlUp).Offset(1，0) r.Copy r2.PasteSpecial xlValues