在vlookup中使用vba时出错
问题描述:
我收到以下代码的错误。该错误是在最后line.Here TargetColNO显示是37在vlookup中使用vba时出错
Sub lookup1()
'
' lookup1 Macro
'
Dim ColIndex As String
Dim par As String
Dim Lookup_Range As Range
Dim sal As Integer
ColIndex = "C" & TargetColNo
'Range("O2").Select
Cells(3, LastCol + 1).Select
ColName = ColumnLetter(TargetColNo)
par = "$" & ColName
'Set Lookup_Range = Range("[NBH_MPP_20161104]NBH!$A:par")
'ActiveCell.FormulaR1C1 = "=VLOOKUP(C1,[NBH_MPP_20161104.xls]NBH!C1:ColName,TargetColNo,0)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(A3,[NBH_MPP_20161104.xls]NBH!$A:par,TargetColNo,0)"
答
由于TargetColNo
也par
的变量,把他们的双引号外面,所以修改你的最后一行来自:
ActiveCell.FormulaR1C1 = "=VLOOKUP(A3,[NBH_MPP_20161104.xls]NBH!$A:par,TargetColNo,0)"
要:
ActiveCell.FormulaR1C1 = "=VLOOKUP(A3,[NBH_MPP_20161104.xls]NBH!$A:" & par & "," & TargetColNo & ",0)"
编辑1:最好是避免使用Select
,因为我n您不使用R1C1
结构公式,你可以替换的最后几行:
Cells(3, LastCol + 1).Formula = "=VLOOKUP(A3,[NBH_MPP_20161104.xls]NBH!$A:" & par & "," & TargetColNo & ",0)"
我猜你会需要大约TargetColNo报价,或“&TargetColNo&”如果它是一个变量。 – Rdster
@Utkarsh Jain尝试下面我的答案中的代码,并让我知道它是否适合你 –