需要一个宏来搜索记录并将其从一个工作表更新到另一个工作表 [英] Need a macro to search and update the record from one worksheet to another
本文介绍了需要一个宏来搜索记录并将其从一个工作表更新到另一个工作表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要一个宏代码来从单元格1到1000中的sheet1中搜索内容(它可以是数字或字母数字),并从sheet2中搜索相同的文本.如果发现,那么我需要更新与相邻单元格相对应的内容. 例如:
I need a macro code to search the content(it may be numeric or aplhanumeric) from sheet1 in the cell 1 to 1000 and to search the same text from sheet2. and if it founds then i need to update the content corresponding to adjacent cell. eg:
sheet1
1024 D
505A
6057 C
sheet2
1024 D
6057 C
推荐答案
假定查找值在A列中,要复制的值在B列中.
Assuming the look up values are in column A and the value to copy is in column B.
Sub FindValues()
Dim lookUpSheet As Worksheet, updateSheet As Worksheet
Dim valueToSearch As String
Dim i As Integer, t As Integer
Set lookUpSheet = Worksheets("sheet1")
Set updateSheet = Worksheets("sheet2")
'get the number of the last row with data in sheet1 and in sheet2
lastRowLookup = lookUpSheet.Cells(Rows.Count, "A").End(xlUp).Row
lastRowUpdate = updateSheet.Cells(Rows.Count, "A").End(xlUp).Row
'for every value in column A of sheet2
For i = 1 To lastRowUpdate
valueToSearch = updateSheet.Cells(i, 1)
'look the value in column A of sheet1
For t = 1 To lastRowLookup
'if found a match, copy column B value to sheet1 and proceed to the next value
If lookUpSheet.Cells(t, 1) = valueToSearch Then
updateSheet.Cells(i, 2) = lookUpSheet.Cells(t, 2)
Exit For
End If
Next t
Next i
End Sub
这篇关于需要一个宏来搜索记录并将其从一个工作表更新到另一个工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文