Excel Vba .find [英] Excel Vba .find
问题描述
大家好,我是Mark,我是VBA的新手.
Hi the names Mark and I am a new to VBA.
我有两个工作簿,其中包含需要比较的数据.我目前正在使用此代码进行比较.它可以正常工作并提供我所需的结果.
I have two workbooks that contain data that needs to be compared. I am currently using this code to do the comparisons. It works and gives the results I require.
我要添加的功能是能够在第二个工作簿范围 D:D 的描述中进行搜索,然后将匹配项带回到单元格 Cells(rw,4 >)在我的工作簿中.该信息将放置在 Cells(rw,29 )
What I am looking to add is the ability to search within a description in my second workbook range D:D and bring back matches to the cell Cells(rw, 4) in my active work book. This information would be placed in Cells(rw, 29)
我已经研究了查找功能,但是无法使其在两个工作簿中都能使用.这里的挑战是我搜索的工作簿或活动的工作簿名称发生更改.
I have researched the find function but can not get it to work across two workbooks. The challenge here being that the work book I search from or active workbooks name changes.
Sub VlookUpExampleDifferBooks()
'This example look up table in different book and sheet (TABLE 1 - ActiveSheet, TABLE 2 - CMF.xlxs and sheet1)
'Validate_Down Macro
Dim LastRow As Long
Dim rw As Long
Dim mx As Integer
Application.ScreenUpdating = False 'Find Last Row
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
For rw = 11 To LastRow ' Loop until rw = Lastrow
Cells(rw, 27) = "'" & Cells(rw, 4)
Cells(rw, 28) = "'" & Cells(rw, 2)
Cells(rw, 25) = Application.VLookup(Cells(rw, 27), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("A:D"), 3, False) ' Vlookup function
Cells(rw, 26) = Application.VLookup(Cells(rw, 28), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("B:D"), 2, False) ' Vlookup function
Cells(rw, 20) = Application.VLookup(Cells(rw, 18), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("C:D"), 2, False) ' Vlookup function
Cells(rw, 19) = Application.VLookup(Cells(rw, 18), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("C:E"), 3, False) ' Vlookup function
If IsError(Cells(rw, 25)) Then Cells(rw, 25) = ""
If IsError(Cells(rw, 26)) Then Cells(rw, 26) = ""
If Cells(rw, 25) <> Cells(rw, 26) Then Cells(rw, 18) = Cells(rw, 25) & "/" & Cells(rw, 26)
If Cells(rw, 25) = Cells(rw, 26) Then Cells(rw, 18) = "'" & Cells(rw, 25)
If Cells(rw, 25) <> Cells(rw, 26) And Cells(rw, 26) = "" Then Cells(rw, 18) = "'" & Cells(rw, 25)
If Cells(rw, 25) <> Cells(rw, 26) And Cells(rw, 25) = "" Then Cells(rw, 18) = "'" & Cells(rw, 26)
If IsError(Cells(rw, 20)) Then Cells(rw, 20) = ""
If IsError(Cells(rw, 19)) Then Cells(rw, 19) = ""
Next
推荐答案
我相信您在使用FIND
函数时遇到了麻烦,因为它旨在在单个单元格中查找值/字符串.您正在尝试搜索整个列.解决问题的蛮力方法是遍历原始数据列&检查每个单元格是否符合要求.我在下面的代码中包含了一个示例.
I believe you are having trouble using the FIND
function because it is designed to find values/strings within a single cell. You are attempting to search through an entire column. A brute force way to solve your problem is to loop through your raw data column & check each cell for the desired match. I've included an example in your code below.
我使用的是instr()
函数而不是find
.它们的工作原理几乎相同,但是instr()
在VBA中使用更方便.
I used the instr()
function rather than find
. They work much the same, but instr()
is a bit more convenient for use in VBA.
Sub VlookUpExampleDifferBooks()
'This example look up table in different book and sheet (TABLE 1 - ActiveSheet, TABLE 2 - CMF.xlxs and sheet1)
'Validate_Down Macro
Dim LastRow As Long
Dim rw As Long
Dim mx As Integer
Application.ScreenUpdating = False 'Find Last Row
LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
For rw = 11 To LastRow ' Loop until rw = Lastrow
Cells(rw, 27) = "'" & Cells(rw, 4)
Cells(rw, 28) = "'" & Cells(rw, 2)
Cells(rw, 25) = Application.VLookup(Cells(rw, 27), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("A:D"), 3, False) ' Vlookup function
Cells(rw, 26) = Application.VLookup(Cells(rw, 28), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("B:D"), 2, False) ' Vlookup function
Cells(rw, 20) = Application.VLookup(Cells(rw, 18), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("C:D"), 2, False) ' Vlookup function
Cells(rw, 19) = Application.VLookup(Cells(rw, 18), Workbooks("CMF Export.xlsx").Sheets("Sheet1").Columns("C:E"), 3, False) ' Vlookup function
'Loop through all cells in column D and check each one for the value in cells(rw,4)
For Each testcell In Workbooks("CMF Export.xlsx").Sheets("Sheet1").Range("D:D")
If InStr(1, testcell.Value, Cells(rw, 4).Value, vbTextCompare) <> 0 Then
Cells(rw, 29).Value = Cells(rw, 4).Value
Exit For
End If
Next testcell
If IsError(Cells(rw, 25)) Then Cells(rw, 25) = ""
If IsError(Cells(rw, 26)) Then Cells(rw, 26) = ""
If Cells(rw, 25) <> Cells(rw, 26) Then Cells(rw, 18) = Cells(rw, 25) & "/" & Cells(rw, 26)
If Cells(rw, 25) = Cells(rw, 26) Then Cells(rw, 18) = "'" & Cells(rw, 25)
If Cells(rw, 25) <> Cells(rw, 26) And Cells(rw, 26) = "" Then Cells(rw, 18) = "'" & Cells(rw, 25)
If Cells(rw, 25) <> Cells(rw, 26) And Cells(rw, 25) = "" Then Cells(rw, 18) = "'" & Cells(rw, 26)
If IsError(Cells(rw, 20)) Then Cells(rw, 20) = ""
If IsError(Cells(rw, 19)) Then Cells(rw, 19) = ""
Next
希望这会有所帮助!
-雅各布
这篇关于Excel Vba .find的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!