将变量放入Excel VBA函数中时出错

Question

Sub airtableCleaner() 
    Dim x As Integer 
    Dim argCounter As Integer 
    Dim A As String 
    Dim B As String 
    Dim folderLocation As Variant 
    Dim Answer As VbMsgBoxResult 

'Ask user if they want to run macro 
Answer = MsgBox("Do you want to run this macro? Please use airtable Download as CSV - Column 1: Primary key, Column 2: Airtable Linkz", vbYesNo, "Run Macro") 
If Answer = vbYes Then 

folderLocation = Application.InputBox("Enter a folder location where your image assets will be") 


'Cleanup to just amazons3 dl.airtable links 
Columns("B:B").Select 
Selection.Replace What:="* ", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 

'Count Cells 
Range("B2").Activate 
Do 
    If ActiveCell.Value = "" Then Exit Do 
    ActiveCell.Offset(1, 0).Activate 
    argCounter = argCounter + 1 

Loop 

'Copy Image Links to new cells to format in Column C 
Columns("B:B").Select 
Selection.Copy 
Columns("C:C").Select 
ActiveSheet.Paste 
Application.CutCopyMode = False 

'Clean up links to only have names in Column C 
Selection.Replace What:="https://dl.airtable.com/", Replacement:="", _ 
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _ 
False, ReplaceFormat:=False 



'Create Batch on Column D 
    Range("D2").Select 
ActiveCell.FormulaR1C1 = _ 
    "=CONCATENATE(""COPY "",CHAR(34),RC[-1],CHAR(34),"" "", CHAR(34), [" & folderLocation & "],RC[-3],"".png"",CHAR(34))" 
Range("D2").Select 
Selection.AutoFill Destination:=Range("D2:D9") 
Range("D2:D9").Select 

'Delete header row 1 information 
Rows("1:1").Select 
Selection.Delete Shift:=xlUp 

'Repaste values back into column D removing formulas 
    Columns("D:D").Select 
Selection.Copy 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
Application.CutCopyMode = False 


End If 
End Sub 

我有这套excel VBA代码。我发现了一个错误

运行时错误“1004”应用程序定义或对象定义的错误

在这条线

"=CONCATENATE(""COPY "",CHAR(34),RC[-1],CHAR(34),"" "", CHAR(34), [" & folderLocation & "],RC[-3],"".png"",CHAR(34))"

我已经设置folderLocation可变值作为c:\doge并使反射此

我的鳕鱼一个文件夹Ë工作正常，直到我介绍了一个Excel函数

什么我错在这里做的内部变量？

EDIT

，这是我使用

=CONCATENATE("COPY ",CHAR(34),C5,CHAR(34)," ", CHAR(34), "c:\doge\",A5,".png",CHAR(34))

其中c:\doge\是我想输入在用户输入的地方原来的公式。

Answer 1

这是你正在尝试？

folderLocation = "c:\doge\" 

Range("D2").Formula = "=CONCATENATE(""COPY "",CHAR(34),C5,CHAR(34),"" "", CHAR(34), " & _ 
         Chr(34) & folderLocation & Chr(34) & ",A5,"".png"",CHAR(34))"