我一直在解决下面的问题有一段时间了,现在我得到了一个非常基本的版本,没有错误处理。我的目标是有这个宏从个人宏工作簿运行。
我正在从各种来源导入管道分隔的文本文件,标题中的格式都不匹配(一些提供商决定玩弄整个布局)。有了这个问题,我创建了一个名为Reference(又名Map)的Excel工作簿,其中包含传入文件的布局和标准化/更正的列名称格式。
对我来说,好消息是,文件ID将始终位于A列。我每个月需要处理大约65个文件,因此我需要最大限度地减少所有可能的步骤,因此需要关闭参考工作簿。
有了这个,我在网上四处寻找,并把大部分的解决方案放在一起,以根据位于A3中的ID拉入新的标题。然而,困境仍然存在,有时A3上的ID将不存在于参考工作簿上-我需要让vlookup向下移动到下一行,直到结果不等于#N/A或0或空白。
在这一点上,我得到了'做循环直到'找到正确的行的第一个匹配-工程完美的任何代码遵循它。
一旦vlookup找到具有现有ID的行,请运行下面的代码段以填充剩余的标头。下一步的唯一副作用是,由于某种原因,rID偏移+1行,如果最后一行不包含匹配的ID,则撤消“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:\Users\x165422\Desktop\New 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
2条答案
按热度按时间1l5u6lss1#
我会尽量帮助一点,因为你表现出努力学习。我会给予你一些有用的提示,关于整体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
。现在你知道了,这是不好的。所以,在这种情况下,如果您希望它是string
而不是range
,则应该将其重命名为rng1
,并学习始终使用这种命名约定。wb2
也是如此。1.始终使用
Option Explicit
作为代码模块中的最高行,在所有sub
之前。这只是防止打字错误。这实际上是一个不可或缺的行。我总是使用它,每个人都应该这样做。在这样做之后,我看到你的代码将无法运行,因为cell
变量没有定义。使用Dim cell As Range
,因为cell实际上是一个range
类型的对象。在这种情况下,Set
被省略了,因为For Each ... In ... Next
循环为你做了这件事。现在你的实际问题。
1.您会发现
Worksheet.UsedRange
对于确定列数非常有用,它表示工作表中从A1到最后使用的单元格的区域(这实际上是最后一个使用的列和最后一个使用的行的交集)。假设您的数据从列A开始,并且当它结束时,右侧没有不属于任何列的数据,你可以使用ActiveSheet.UsedRange.Columns.Count
来获取UsedRange
中的列数,希望在你的工作表中。1.我首先提到
UsedRange
,因为我想让你熟悉它。我们将使用它来解决vlookup
的问题。所以在vlookup
之前,我建议的第一步是找到具有您ID的单元格。我们应该首先使用dim
变量,例如rng3
:然后我的建议是找到ID为A的单元格,从A3开始循环遍历列A中的单元格,但直到列的末尾才循环,因为有1 m行,但直到我们到达最后一个实际使用的行:
现在,我们已经有了你的ID所在的单元格,我们可以在其中进行查找:
我希望这里没有错别字,因为我还没有测试它。但是,我已经仔细检查了代码,它看起来不错。
brqmpdu12#
我得到了它的工作: