VBA在另一列的列中搜索值,如果找不到,则显示该值,如果找到,则将偏移值复制到偏移量输入中 [英] VBA Search value in column in another column, if not found show which, if found copy offset value to offset imput
本文介绍了VBA在另一列的列中搜索值,如果找不到,则显示该值,如果找到,则将偏移值复制到偏移量输入中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
能帮我做个VBA脚本,该脚本将搜索Sheet1 H:H列中的值单元格(每行都有数据),如果它在Sheet 2 H:H中找到值,它将复制工作表1的偏移量-6和将偏移量-6粘贴到工作表2中.
Can you help me make VBA script that will search values cells in column Sheet1 H:H (every row with data), if it finds the value in Sheet 2 H:H, it will copy offset -6 from sheet 1 and paste offset -6 in sheet 2.
如果找不到任何内容,它将告诉我没有找到哪些值.
If it dont find anything it will tell me which values it didnt find.
这就是我目前所能承受的,可以正常工作,但不是最佳选择,首先,我没有获得"NOT"发现值的信息,如果找不到,它将仍然覆盖并复制该项目.
THis is what i have so fare, working but not optimal, firstly i dont get information of the "NOT" Found values, and if it is not found, it will just overwrite and copy that item anyway.
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim oCell As Range
Dim i As Long
i = 2
Set ws1 = ThisWorkbook.Sheets("Data")
Set ws2 = ThisWorkbook.Sheets("Mellomlagring")
Do While ws1.Cells(i, 1).Value <> ""
Set oCell = ws2.Range("H:H").Find(what:=ws1.Cells(i, 8))
If Not oCell Is Nothing Then ws1.Cells(i, 2) = oCell.Offset(0, -6)
i = i + 1
Loop
Set ws1 = Nothing
Set ws2 = Nothing
谢谢您的帮助
推荐答案
尝试一下:
Sub tgr()
Dim wb As Workbook
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rSourceHCol As Range
Dim rSourceHCell As Range
Dim rDestHCol As Range
Dim rFound As Range
Dim sFirst As String
Dim sNotFound As String
Set wb = ActiveWorkbook
Set wsSource = wb.Sheets("Sheet1")
Set wsDest = wb.Sheets("Sheet2")
Set rSourceHCol = wsSource.Range("H2", wsSource.Cells(wsSource.Rows.Count, "H").End(xlUp))
Set rDestHCol = wsDest.Range("H2", wsDest.Cells(wsDest.Rows.Count, "H").End(xlUp))
If rSourceHCol.Row < 2 Then
MsgBox "No values present in column H of source sheet " & wsSource.Name
Exit Sub
ElseIf rDestHCol.Row < 2 Then
MsgBox "No values present in column H of destination sheet " & wsDest.Name
Exit Sub
End If
For Each rSourceHCell In rSourceHCol.Cells
Set rFound = rDestHCol.Find(rSourceHCell.Value, rDestHCol.Cells(rDestHCol.Cells.Count), xlValues, xlWhole)
If rFound Is Nothing Then
sNotFound = sNotFound & Chr(10) & rSourceHCell.Value
Else
sFirst = rFound.Address
Do
rFound.Offset(, -6).Value = rSourceHCell.Offset(, -6).Value
Set rFound = rDestHCol.FindNext(rFound)
Loop While rFound.Address <> sFirst
End If
Next rSourceHCell
If Len(sNotFound) = 0 Then
MsgBox "All values from source data accounted for and updated in destination"
Else
MsgBox "The following values in the source data were not found in destination:" & sNotFound
End If
End Sub
这篇关于VBA在另一列的列中搜索值,如果找不到,则显示该值,如果找到,则将偏移值复制到偏移量输入中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文