excel 基于唯一ID查找和替换行

bq3bfh9z 于 2023-03-13 发布在其他

关注(0)|答案(1)|浏览(116)

我确信这是非常简单的，但我是VBA的新手，我正在尝试创建一个宏来复制“临时”表中的整个第2行

并将整行替换为“QuestLog”工作表上A列中的相应值

我试过用谷歌搜索，但我被卡住了，

Sub UpdateQuest()
    
    Dim SrchSht As Worksheet
      Dim SearchValue, OutPlace
      Set SrchSht = Sheets("QuestLog")
      SearchValue = Sheets("Temp2").Range("A2").Value
      Set c = SrchSht.Range("A:A").Find(what:=SearchValue, LookIn:=xlValues)
      
      If Not c Is Nothing Then
        c.Value = Sheets("Temp2").Range("A2").Value
        c.Offset(0, 1) = Sheets("Temp2").Range("B2")
        c.Offset(0, 2) = Sheets("Temp2").Range("C2")
        c.Offset(0, 3) = Sheets("Temp2").Range("D2")
        c.Offset(0, 4) = Sheets("Temp2").Range("E2")
        c.Offset(0, 5) = Sheets("Temp2").Range("F2")
        c.Offset(0, 6) = Sheets("Temp2").Range("G2")
      Else
        MsgBox "Quest ID not found"
      End If
      
    End Sub

干杯，
丹

excel

来源：https://stackoverflow.com/questions/75695303/find-and-replace-a-row-based-on-unique-id

1条答案

按热度按时间

ubof19bj1#

Sub CopyDataToQuestLog()
    Dim searchValue As String
    Dim searchRange As Range
    Dim foundCell As Range
    Dim temp2Sheet As Worksheet
    Dim questLogSheet As Worksheet
    Dim copyRange As Range
    
    ' Set references to the relevant sheets
    Set temp2Sheet = ThisWorkbook.Sheets("Temp2")
    Set questLogSheet = ThisWorkbook.Sheets("QuestLog")
    
    ' Set the search value to the value in cell A2 of Temp2 sheet
    searchValue = temp2Sheet.Range("A2").Value
    
    ' Set the search range to the first column of QuestLog sheet
    Set searchRange = questLogSheet.Range("A:A")
    
    ' Find the search value in the search range
    Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    
    ' If the search value was found, copy the data from Temp2 sheet to QuestLog sheet
    If Not foundCell Is Nothing Then
        Set copyRange = temp2Sheet.Range("B2:G2")
        copyRange.Copy questLogSheet.Range("B" & foundCell.Row)
    Else
        MsgBox "Quest ID not found"
    End If
End Sub

赞(0）回复(0）举报 2023-03-13

我来回答

excel 基于唯一ID查找和替换行

1条答案

相关问题

热门标签

最新问答