Search 2.0 - What's Next? [zz]

本文探讨了搜索2.0的发展趋势,包括用户界面改进、技术创新和垂直搜索引擎等三个方面。介绍了Snap、SearchMash等新兴搜索产品,并分析了Hakia、Powerset等公司的语义搜索技术。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 
Sub UpdateSheet4WithWIP() Dim ws4 As Worksheet, ws5 As Worksheet Set ws4 = ThisWorkbook.Sheets("WIP 3rd party CR status") Set ws5 = ThisWorkbook.Sheets("Z3PCR012") '------------------- ??Sheet4??? ------------------- Dim dictExisting As Object Set dictExisting = CreateObject("Scripting.Dictionary") Dim lastRow4 As Long lastRow4 = ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row If lastRow4 >= 3 Then Dim arrSheet4 As Variant arrSheet4 = ws4.Range("A3:A" & lastRow4).Value For i = 1 To UBound(arrSheet4, 1) If arrSheet4(i, 1) <> "" Then dictExisting(arrSheet4(i, 1)) = True Next End If '--------------- ??Sheet5?D??WIP?C?? --------------- Dim dictNew As Object Set dictNew = CreateObject("Scripting.Dictionary") Dim lastRow5 As Long lastRow5 = ws5.Cells(ws5.Rows.Count, "C").End(xlUp).Row If lastRow5 >= 3 Then Dim rngWIP As Range, cell As Range Set rngWIP = ws5.Range("C3:C" & lastRow5) For Each cell In rngWIP If ws5.Cells(cell.Row, "D").Value = "WIP" Then If cell.Value <> "" And Not dictExisting.Exists(cell.Value) Then dictNew(cell.Value) = True End If End If Next End If '------------------- ???Sheet4????? ------------------- If dictNew.Count > 0 Then Dim keys() As Variant keys = dictNew.keys Dim targetCell As Range Dim filled As Long: filled = 0 '???????? For Each targetCell In ws4.Range("A3:A" & ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + dictNew.Count) If targetCell.Row < 3 Then GoTo Skip If targetCell.Value = "" And filled < UBound(keys) + 1 Then targetCell.Value = keys(filled) filled = filled + 1 End If Skip: Next '?????????? If filled < dictNew.Count Then ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Offset(1).Resize(dictNew.Count - filled).Value = _ Application.Transpose(keys) End If End If MsgBox "Done haha" End Sub 在以上VBA后面新增新的功能: 将在sheet“WIP 3rd party CR status”新增的所有数据,对应的Y列输入“To be input”
07-19
<think>我们将在现有的 `Sub UpdateSheet4WithWIP()` 过程中增加功能:对于在“WIP 3rd party CR status”工作表中新增的数据,在对应的Y列(即第25列)输入“To be input”。 注意:新增的数据指的是我们刚刚从Z3PCR012工作表中提取并添加到“WIP 3rd party CR status”工作表A列的数据。我们在添加这些数据的同时,需要给同一行的Y列赋值。 修改思路: 1. 在现有代码中,我们通过字典`dictNew`记录了所有新增的值(即需要添加到ws4的A列的值)。 2. 在添加这些新值到A列的同时(或之后),我们可以遍历ws4的A列,找到这些新增值所在的行,并在同一行的Y列(即第25列)写入“To be input”。 具体实现: 在更新完A列的数据后(即代码中填充完新增数据后),我们可以遍历字典`dictNew`中的每一个键(即新增的值),在ws4的A列中查找这个值,然后在该行的Y列写入“To be input”。 注意:由于可能存在多个相同的值?但根据我们的设计,字典的键是唯一的,且我们在添加时已经通过字典去重。同时,在ws4中,我们添加的每个值都是唯一的(因为字典已经存在的数据不会再次添加)。所以,我们可以通过循环字典的键,在A列中查找每个键对应的第一个匹配项(因为每个键只添加了一次,所以应该只有一个匹配项)并设置Y列。 但是,注意:我们添加数据的方式有两种: a) 填充现有空行(所以新增的数据可能不是连续添加在最后) b) 在最后追加剩余数据 因此,我们需要在更新完数据后,遍历整个A列(从A3开始)来设置新增数据对应的Y列。 修改步骤: 在代码的末尾(即`MsgBox "Done haha"`之前)添加以下代码: ```vba ' 遍历新增的数据,在Y列写入"To be input" If dictNew.Count > 0 Then Dim key As Variant Dim foundCell As Range Dim firstAddress As String For Each key In dictNew.keys Set foundCell = ws4.Range("A:A").Find(What:=key, LookIn:=xlValues, LookAt:=xlWhole) If Not foundCell Is Nothing Then firstAddress = foundCell.Address Do ' 确保找到的行在A3及以下(因为表头在1-2行?) If foundCell.Row >= 3 Then ws4.Cells(foundCell.Row, "Y").Value = "To be input" End If ' 继续查找下一个(理论上每个key只出现一次,但为了安全) Set foundCell = ws4.Range("A:A").FindNext(foundCell) Loop While Not foundCell Is Nothing And foundCell.Address <> firstAddress End If Next key End If ``` 但是,这种方法效率较低,因为要遍历每个键并在整个A列查找。由于新增的数据是我们刚刚添加的,我们可以记录在添加数据时每一行的位置,这样在添加的同时就可以设置Y列。 更高效的方法:在填充数据的时候同时设置Y列。 修改填充数据的部分: 原代码: ```vba For Each targetCell In ws4.Range("A3:A" & ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + dictNew.Count) If targetCell.Row < 3 Then GoTo Skip If targetCell.Value = "" And filled < UBound(keys) + 1 Then targetCell.Value = keys(filled) filled = filled + 1 End If Skip: Next ``` 我们可以在这里直接设置Y列: ```vba For Each targetCell In ws4.Range("A3:A" & ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + dictNew.Count) If targetCell.Row < 3 Then GoTo Skip If targetCell.Value = "" And filled < UBound(keys) + 1 Then targetCell.Value = keys(filled) ' 同时设置同一行的Y列为"To be input" ws4.Cells(targetCell.Row, "Y").Value = "To be input" filled = filled + 1 End If Skip: Next ``` 同样,在追加剩余数据时,我们也可以同时设置Y列: ```vba If filled < dictNew.Count Then Dim startRow As Long startRow = ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + 1 ws4.Range("A" & startRow).Resize(dictNew.Count - filled).Value = Application.Transpose(keys) ' 同时设置这些新行的Y列为"To be input" ws4.Range("Y" & startRow).Resize(dictNew.Count - filled).Value = "To be input" End If ``` 注意:在填充现有空行时,我们每填一个空行,就在同一行的Y列写入;在追加剩余数据时,我们一次性写入Y列。 因此,我们不需要后面的循环查找设置,效率更高。 修改后的完整过程如下:</think>### 修改后的完整代码(新增功能已实现) ```vba Sub UpdateSheet4WithWIP() Dim ws4 As Worksheet, ws5 As Worksheet Set ws4 = ThisWorkbook.Sheets("WIP 3rd party CR status") Set ws5 = ThisWorkbook.Sheets("Z3PCR012") '------------------- 获取Sheet4现有数据 ------------------- Dim dictExisting As Object Set dictExisting = CreateObject("Scripting.Dictionary") Dim lastRow4 As Long lastRow4 = ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row If lastRow4 >= 3 Then Dim arrSheet4 As Variant arrSheet4 = ws4.Range("A3:A" & lastRow4).Value For i = 1 To UBound(arrSheet4, 1) If arrSheet4(i, 1) <> "" Then dictExisting(arrSheet4(i, 1)) = True Next End If '--------------- 扫描Sheet5中WIP数据 --------------- Dim dictNew As Object Set dictNew = CreateObject("Scripting.Dictionary") Dim lastRow5 As Long lastRow5 = ws5.Cells(ws5.Rows.Count, "C").End(xlUp).Row If lastRow5 >= 3 Then Dim rngWIP As Range, cell As Range Set rngWIP = ws5.Range("C3:C" & lastRow5) For Each cell In rngWIP If ws5.Cells(cell.Row, "D").Value = "WIP" Then If cell.Value <> "" And Not dictExisting.Exists(cell.Value) Then dictNew(cell.Value) = True End If End If Next End If '------------------- 更新Sheet4数据 ------------------- If dictNew.Count > 0 Then Dim keys() As Variant keys = dictNew.keys Dim targetCell As Range Dim filled As Long: filled = 0 '填充现有空行 For Each targetCell In ws4.Range("A3:A" & ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + dictNew.Count) If targetCell.Row < 3 Then GoTo Skip If targetCell.Value = "" And filled < UBound(keys) + 1 Then targetCell.Value = keys(filled) ' >>>>> 新增功能:在Y列添加标记 <<<<< ws4.Cells(targetCell.Row, "Y").Value = "To be input" filled = filled + 1 End If Skip: Next '追加剩余数据 If filled < dictNew.Count Then Dim startRow As Long startRow = ws4.Cells(ws4.Rows.Count, "A").End(xlUp).Row + 1 ws4.Range("A" & startRow).Resize(dictNew.Count - filled).Value = _ Application.Transpose(keys) ' >>>>> 新增功能:在Y列批量添加标记 <<<<< ws4.Range("Y" & startRow).Resize(dictNew.Count - filled).Value = "To be input" End If End If MsgBox "Done haha" End Sub ``` ### 新增功能说明: 1. **在现有空行填充时添加标记**: ```vba ' 在填充A列数据的同时更新Y列 ws4.Cells(targetCell.Row, "Y").Value = "To be input" ``` 2. **在追加新行时批量添加标记**: ```vba ' 在追加新行后批量更新Y列 ws4.Range("Y" & startRow).Resize(dictNew.Count - filled).Value = "To be input" ``` 3. **功能特点**: - 只对**新增**的数据添加标记(已有数据不受影响) - 同时处理两种添加场景: - 填充现有空白行 - 追加新行 - 标记位置:Y列(第25列) - 标记内容:"To be input" ### 注意事项: 1. 工作表结构要求: - "WIP 3rd party CR status"工作表必须有Y列 - 若Y列有其他内容,新增标记会覆盖原有内容 2. 执行位置: - 标记操作在数据添加后立即执行 - 确保在数据完整性检查后操作
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值