VBA字典以搜索，匹配和粘贴：未知错误 [英] VBA Dictionary to search, match, and paste: Unknown Error

问题描述

好吧，所以我从

我正在使用此代码来解决诸如索引匹配或vlookup之类的公式（数据过多，工作簿被设计为使用由不知道在添加新记录时如何向下拖动公式的个人）。

  Sub date（）
 
 Application.ScreenUpdating = False 
 
 Dim AVals作为新字典
 Dim i长，j长，lastRow1长，lastRow2长
 Dim sh_insp， sh_2018 As Worksheet 
 Dim MyName As String 
 
 set sh_insp =活动表
 set sh_2018 = Sheets（ 2018）
 
与sh_insp 
 lastRow1 = .Range（ A：A）。Rows计数'电子表格
中的最后一行lastRow1 = .Cells（lastRow1，7）.End（xlUp）。行'G列中最后使用的行
'加载AVal dict 
对于j = 18 To lastRow1 
 MyName = .Cells（j，7）.Value 
如果Len（MyName）> 0然后AVals.Add MyName，.Cells（j，7）.Value 
接下来j 
以
 
结尾，以sh_2018 
 lastRow2 = .Range（ A： A）。Rows.Count 
 lastRow2 = .Cells（lastRow2，7）.End（xlUp）.Row'G列中最后使用的行
对于i = 18到lastRow2 
 MyName = .Cells（i，7）.Value 
如果AVals.Exists（MyName）然后
 .Cells（i，18）.Value = AVals.Item（MyName）
如果
接下来i 
以
结尾Application.ScreenUpdating = True 
 
 End Sub 
  

在 Dim AVals作为新字典处出现未定义的用户定义类型 ，这使我什至无法真正查看它是否有效或不。另外，有时还没有分配任务，而您分配了任务但未完成。我不希望它擦除任何人的数据，仅在任务编号匹配时才添加数据。

解决方案

替换此行：

 如果Len（MyName）> 0然后用AVals.Add MyName，.Cells（j，7）.Value 
  

p>

 如果Len（MyName）> 0并且Len（.Cells（j，18））> 0然后AVals.Add MyName，.Cells（j，18）.Value 
  

Ok, so I pulled this code from here, and have been trying to adapt it with little success. I may simply be misunderstanding the original or am missing some other fundamental flaw (like perhaps the code was written for an older iteration of excel).

I have a Master Sheet (2018) with a lot of different rows with different tasks assigned to an inspector. Each task has a number in column G that is unique, and an inspector can have more than one task--but no task has more than one inspector. Column G is unique--the numbers do not repeat. Each inspector has his/her own sheet that is updated using a different excel--inspector sheets are formatted exactly as the master sheet is, all columns are the same. The inspector will put in the date, in their own sheet, a task is completed (date goes in Column R) and I'm trying to use this code so that when an inspector completes a task and put in the date, he clicks a button and that date is copied to column R in the master sheet in the correct row that matches the correct task number.

Like so:

I'm using this code to work around using a formula like index match or vlookup (far too much data and the workbook is designed to be used by individuals who do not know how to drag down a formula when new records are added).

Sub dates()

Application.ScreenUpdating = False

Dim AVals As New Dictionary
Dim i As Long, j As Long, lastRow1 As Long, lastRow2 As Long
Dim sh_insp, sh_2018 As Worksheet
Dim MyName As String

Set sh_insp = Activesheet
Set sh_2018 = Sheets("2018")

With sh_insp
    lastRow1 = .Range("A:A").Rows.Count 'last row in spreadsheet
    lastRow1 = .Cells(lastRow1, 7).End(xlUp).Row 'last used row in column G
    'load the AVal dict
    For j = 18 To lastRow1
        MyName = .Cells(j, 7).Value
        If Len(MyName) > 0 Then AVals.Add MyName, .Cells(j, 7).Value
    Next j
End With

With sh_2018
    lastRow2 = .Range("A:A").Rows.Count
    lastRow2 = .Cells(lastRow2, 7).End(xlUp).Row 'last used row in column G
    For i = 18 To lastRow2
        MyName = .Cells(i, 7).Value
        If AVals.Exists(MyName) Then
            .Cells(i, 18).Value = AVals.Item(MyName)
        End If
     Next i
End With
Application.ScreenUpdating = True

End Sub

I'm getting a 'User-defined type not defined' at the Dim AVals as New Dictionary which prevents me from really even seeing if it works or not. Also, sometimes a task isn't assigned yet and you have tasks assigned but not completed. I don't want it to erase anyone's data only add data when a task number is matched. Does this make sense?

解决方案

Replace this line:

If Len(MyName) > 0 Then AVals.Add MyName, .Cells(j, 7).Value

with this:

If Len(MyName) > 0 And Len(.Cells(j, 18)) > 0 Then AVals.Add MyName, .Cells(j, 18).Value

这篇关于VBA字典以搜索，匹配和粘贴：未知错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！