我正在处理一个excel文件,我有一个我需要搜索的单词列表,如果我找到它,我必须突出显示其列。
我想使用ctrl f,但我只能在当时复制并粘贴一个单词,所以如果有一种方法可以通过使用VBA或条件格式来自动化此任务。
我已经看过网络,但是解决方案并不能很好地解决我的问题。
我在mrexcel.com上找到了它(查找记录并放入摘要表中)并迅速对其进行了修改(感谢Brianb)。
观看您的标签的命名,就像它们在代码中的命名一样。这只是为了快速帮助并向您展示一种方式,我对我的编辑不是很好或进一步评论。
Sub FindRecords()
Dim FromSheet As Worksheet
Dim FromRow As Long
Dim ToSheet As Worksheet
Dim ToRow As Long
Dim FindThis As Variant
Dim FoundCell As Object
'---------------------------------------------------
Application.Calculation = xlCalculationManual
Set FromSheet = ThisWorkbook.Worksheets("DataSheet")
Set ToSheet = ThisWorkbook.Worksheets("Summary")
ToRow = ThisWorkbook.Worksheets("Summary").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
'---------------------------------------------------
'- get user input
FindThis = InputBox("Please enter data to find : ")
If FindThis = "" Then End ' trap Cancel
'---------------------------------------------------
'- clear summary for new data
'ToSheet.Cells.ClearContents
'---------------------------------------------------
' FIND DATA
'-
With FromSheet.Cells
Set FoundCell = .Find(FindThis, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
'------------------------------------------
'- copy data to summary
'Do
FromRow = FoundCell.Row
ToSheet.Cells(ToRow, 1).Value = _
FromSheet.Cells(FromRow, 1).Value
ToSheet.Cells(ToRow, 2).Value = _
FromSheet.Cells(FromRow, 2).Value
ToSheet.Cells(ToRow, 3).Value = _
FromSheet.Cells(FromRow, 3).Value
ToRow = ToRow + 1
'Set FoundCell = .FindNext(FoundCell)
'Loop While Not FoundCell Is Nothing And _
' FoundCell.Address <> FirstAddress
'------------------------------------------
End If
End With
MsgBox ("Done.")
Application.Calculation = xlCalculationAutomatic
FindRecords
End Sub