从已关闭的工作簿填充正确的标头

Question

我已经在下面的问题上工作了一段时间了，我得到了一个非常基本的版本，没有错误处理。我的目标是从个人宏工作簿中运行这个宏。

我正在从各种来源导入管道分隔的文本文件，并且头文件中的格式都不匹配(一些提供商决定使用整个布局)。有了这个问题，我创建了一个名为Reference(又名Map)的Excel工作簿，其中包含传入文件布局和标准化/纠正的列名称格式。

对我来说，好消息是，文件ID将始终在a列。我每个月大约有65个文件需要处理，所以我需要尽量减少所有可能的步骤，因此需要关闭参考工作簿。

有了这个，我在网上看了看，并把大部分的解决方案放在一起，根据位于A3的ID拉入新的标题。然而，仍然存在一个困境，有时A3上的ID将不存在于参考工作簿上-我需要将vlookup移动到下一行，直到结果不等于#N/a或0或空白。

在这一点上，我得到了'Do Loop Until'来找到第一个匹配的正确行-与后面的任何代码一起完美地工作。

一旦vlookup找到具有现有ID的行，就运行下面的代码片段来填充其余的标题。下一步的唯一副作用是，由于某种原因，如果最后一行不包含匹配的ID，则rID偏移+1行，取消'Do Loop until'。

'> Populate remining headers
For Each cell In rng1
        cell.Value = ("=VLOOKUP(" & cID & rID & "," & map & "," & i & ",FALSE)")
        i = i + 1
Next

这是我目前为止写的:

Sub DAc_lookup_headers()
Dim wb1 As Workbook 'Current text/file
Dim map As String 'reference Map
Dim cID As String 'wb1 look up column A
Dim rID As String 'wb1 starting row number
Dim rng1 As Range 'wb1 Collection header range
Dim i As Long 'Index number per cell in range

Set wb1 = ActiveWorkbook
Set rng1 = wb1.ActiveSheet.[A1:G1]
map = ("'C:Usersx165422DesktopNew folder[Reference.xlsx]Ref'!$A$1:$I$13")
rID = 3 'Row where ID is - will increment + 1 if not found
cID = "A" 'Column where ID is
i = 3 'Starting vlookup Index number - to increment per cell in range
'>Look for ID until value is found
Do
    wb1.ActiveSheet.[a1].Value = ("=VLOOKUP(" & cID & rID & "," & map & "," & i & ",FALSE)")
    rID = rID + 1
Loop Until wb1.ActiveSheet.[a1].Text <> "#N/A" Or "0"
'> Populate remining headers
For Each cell In rng1
        cell.Value = ("=VLOOKUP(" & cID & rID & "," & map & "," & i & ",FALSE)")
        i = i + 1
Next
'> Convert to values
With rng1
    .Value = .Value
End With

End Sub

Answer 1

我会尽力帮你一点忙，因为你在努力学习。我将给你一些关于整体VBA编码和你的问题的有用提示。请尽量记住它们。

1. 很难阅读你的代码，因为你的变量命名不好。在VBA中，单元格和它的值之间存在差异。在代码中有rng1和rng2。我看到目前rng1代表细胞的值，rng2 -一个细胞本身。我不确定你是不是故意这样做的，但现在你应该明白了。如果您指的是单元格本身，那么将变量命名为rng2是很容易理解的。但是，如果您指的是单元格的值，则将变量命名为rng1会产生误导。这只是为了更容易地阅读代码。由于您将此变量定义为string，因此我怀疑您希望它接收string类型的值。因此，您应该根据变量的类型来命名变量，即以str...或s...开头的变量，这意味着该变量为string类型，例如strValue或sID。更可取的方法是使用str，因为s可能与single类型混淆(VBA中也有这样的数据类型)。您应该将此逻辑应用于您使用的每个变量。String - str..., long - lng..., integer - int..., double - dbl..., boolean - bln...等。有一种类型比较具体，这里也会用到。这是object类型。对象不仅仅是对象，它们被分解成许多不同的类型，在你的代码中你也使用range和workbook对象。您可以将它们命名为o...或obj...，或者通常最好使用更具体的名称，如rng...(用于范围)或wb...(用于工作簿)。通常数据类型变量的值没有Set子句，而对象总是需要Set以便与一些实际对象相关联，如范围，工作簿，工作表等。在你的代码中，我们看到wb2变量是一个string。现在你知道了，这并不好。因此，在这种情况下，如果您希望将rng1重命名为string而不是range，则应该将其重命名为CC_33，并学会始终使用此命名约定。

2. 始终使用Option Explicit作为代码模块的最上面一行，在所有sub之前。这只是为了防止打字错误。这条线是不可或缺的。我总是用它，每个人都应该用。这样做之后，我看到您的代码将无法运行，因为没有定义cell变量。使用Dim cell As Range，因为cell实际上是range类型的对象。在这种情况下，Set被省略，因为For Each ... In ... Next循环为您做了这个。

现在你的实际问题。

您会发现Worksheet.UsedRange用于确定列数。它表示工作表中从A1到最后使用的单元格的面积(实际上是最后使用的列和最后使用的行的交集)。考虑到你的数据跨度从列A，当它结束时，没有更多的数据到右边不属于任何列，你可以使用ActiveSheet.UsedRange.Columns.Count来获得列的数量在你的UsedRange，并希望在你的工作表。

我首先提到UsedRange，因为我想让你熟悉它。我们将用它来解决vlookup的问题。因此，我建议在vlookup之前的第一步是找到具有您ID的单元格。我们应该首先dim我们将要使用的变量，例如rng3:

Dim rng3 As Range

然后我的建议是找到具有ID的单元格循环通过单元格在列A，开始A3，但不循环，直到列结束，因为有1m行，但直到我们到达最后实际使用的行:

For Each cell In wb1.ActiveSheet.Range("A3:A" & wb1.ActiveSheet.UsedRange.Rows.Count)
    If cell <> "" Then
        Set rng3 = cell
        Exit For 'we found the value, no need to continue looping, so we exit the For loop
    End If
Next 'there is no need to write "cell" after "Next", because VBA knows which loop it is in.

现在，我们有了ID所在的单元格，我们可以在

中进行查找

i = 2 'Starting vlookup Index number per cell in range
For Each cell in rng2
    cell.Value = "=VLOOKUP(" & rng3.Address & "," & wb2 & "," & i & ",FALSE)" 'again, wb2 is not a nice name for String variable
    i = i + 1
Next

我希望这里没有打字错误，因为我没有测试过。然而，我已经仔细检查了代码，它看起来不错。

Answer 2

我让它工作:

Sub DAc_lookup_headers()
Dim wb1 As Workbook 'Current text/file
Dim map As String 'reference Map
Dim cID As String 'wb1 look up column A
Dim rID As String 'wb1 starting row number
Dim rng1 As Range 'wb1 Collection header range
Dim i As Long 'Index number per cell in range

Set wb1 = ActiveWorkbook
Set rng1 = wb1.ActiveSheet.[A1:G1]
map = ("'C:Usersx165422DesktopNew folder[Reference.xlsx]Ref'!$A$1:$I$13")
rID = 2 'Row where ID is - will increment + 1 if not found
cID = "A" 'Column where ID is
i = 3 'Starting vlookup Index number - to increment per cell in range
'>Look for ID until value is found
Do
 rID = rID + 1
    wb1.ActiveSheet.[a1].Value = ("=VLOOKUP(" & cID & rID & "," & map & "," & i & ",FALSE)")
Loop Until wb1.ActiveSheet.[a1].Text <> "#N/A" Or "0"
'> Populate remining headers
For Each cell In rng1
        cell.Value = ("=VLOOKUP(" & cID & rID & "," & map & "," & i & ",FALSE)")
        i = i + 1
Next
'> Convert to values
With rng1
    .Value = .Value
End With

End Sub