在复制和粘贴值之前，从另一个工作簿中的列表中读取大写值

Question

我有一个列表的工作簿。我编写了这段代码，用户可以借此下载最新的文件来更新人口数据。下面的函数允许用户打开文件，代码将在将值粘贴到指定列之前交叉引用列表。

试着更好地解释一下:A列是城市/城镇等的列表，B列是空的。被选中的新工作簿上有城市和人口数量的列表。下面的函数在将值粘贴到空单元格之前检查新工作簿中的列表是否与活动工作簿中的列表匹配。然而，我注意到有时如果有大写字母，它不会拾取它。

我注意到的一个问题是，如果列表的第二个单词中有大写字母，那么它不会被拾取。我知道我需要使用一种形式的逻辑比较，但我不确定如何在下面的代码中应用它。这对其他人来说可能是非常简单的，但我对Excel中的VBA很新鲜。

UCase(…)= UCase(…)

Sub eng_qof()
Application.ScreenUpdating = False
Dim my_Filename As Variant
Dim my_File As Workbook
Dim xcol, ycol, frow As Integer
Dim I As Integer
Dim pop As Range
my_Filename = Application.GetOpenFilename(FileFilter:="Excel Files, *xl*;*.xm*")
If my_Filename <> False Then
Set my_File = Application.Workbooks.Open(my_Filename)



xcol = Workbooks("City list.xlsm").Worksheets("Sheet1").Range("B15:B146").Count

For I = 15 To 15 + xcol
answer1 = Workbooks("City list.xlsm").Worksheets("Sheet1").Range("B" & I).Value
Set pop = myFile.Sheets("list").Range("C" & I).Value
If pop Is Nothing Then
Workbooks("City list.xlsm").Worksheets("Sheet1").Range("D" & I).Value = ""
Else
frow = Workbooks("City list.xlsm").Worksheets("Sheet1").Range("B12:B146").Find(what:=answer1)
my_File.Sheets("list").Range("L" & I).Value = Workbooks("City list.xlsm").Worksheets("Sheet1").Range("D" & I).Value

'my_File.Sheets("list").Range("L15:L149").Copy
'Workbooks("City list.xlsm").Worksheets("Sheet1").Range("D12").PasteSpecial Paste:=xlPasteValues

'my_File.Close False
End If
Next I
End If
Application.ScreenUpdating = True
End Sub

Answer 1

Vlookup不区分大小写，所以您可以试试。

未经测试:

Sub eng_qof()
Dim my_Filename As Variant, ws As Worksheet
Dim my_File As Workbook, c As Range, v, res

my_Filename = Application.GetOpenFilename(FileFilter:="Excel Files, *xl*;*.xm*")
If my_Filename = False Then Exit Sub 'exit if no file selected

Application.ScreenUpdating = False
Set my_File = Application.Workbooks.Open(my_Filename)

Set ws = ThisWorkbook.Worksheets("Sheet1") 'assumes code is running in "City list.xlsm"
'loop cells in ColB
For Each c In ws.Range("B15:B146").Cells
v = c.Value 'value to look up
If Len(v) > 0 Then
'vlookup is not case-sensitive: returns value from Col L or error value if no match
res = Application.VLookup(v, my_File.Sheets("list").Range("B:L"), 11, False)
If Not IsError(res) Then
'match was made
c.EntireRow.Columns("D").Value = res
Else
' ? What to do if no match ?
End If
End If
Next c
my_File.Close False
End Sub