Error putting a variable inside excel VBA function

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

I have this set of excel VBA code. I'm getting an error

Run-time error '1004' Application-defined or object-defined error

At this line

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

I have been setting folderLocation variable value as c:\doge and making a file folder reflecting this

My code was working fine until I introduced a variable inside of an excel function

What am I doing wrong here?

EDIT

this was the original formula I was using

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

where c:\doge\ was the place I wanted to input the user input at.

Can you manually put the formula in the cell and share what that formula is? — Siddharth Rout
– Siddharth Rout, Commented Jun 3, 2017 at 16:40

Siddharth Rout · Accepted Answer · 2017-06-03 16:52:07Z

2

Is this what you are trying?

folderLocation = "c:\doge\"

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

answered Jun 3, 2017 at 16:52

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Tang Over a year ago

Would you happen to know how to format this string? COPY "foo.png" "C:\batch\foo2.png" (original) to COPY "C:\foo.png" "C:\batch\foo2.png"

Siddharth Rout Over a year ago

You mean like this? "COPY ""C:\foo.png"" ""C:\batch\foo2.png""

Vincent Tang Over a year ago

yeah basically that, add a C:\foo in the 2nd argument. Excel VBA formatting is so confusing to me.

Collectives™ on Stack Overflow

Error putting a variable inside excel VBA function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related