Inserting string into a formula in VBA

Question

I am creating a piece of VBa code which will insert a specific formula into my worksheet. As the file is in a shared dropbox I want to create a filepath with a string thisworkbook.path. Though I am unable to use this string in my formula.

The formula that need be pasted is a SUMIF Formula, taking data from another sheet in my workbook, though it keeps asking for a file directory when i try to insert the formula

Sub Monthly_OTB_update()
'
Dim I As Long
Dim J As Integer
Dim H As Integer
Dim A As String
Dim B As String
Dim C As String
Dim D As String
Dim E As String


A = ThisWorkbook.Path
B = "2019"
C = "Monthly OTB 2019"
D = A & B
E = A & C



Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False

    'Go to Last used date and select the next cell
    Sheets("Monthly OTB 2019").Select
    Range("E2").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select

    For I = 1 To 12

    'Fill selection with date and move 1 down
    ActiveCell.FormulaR1C1 = "=TODAY()"
    ActiveCell.Offset(1, 0).Select

        'insert formula to retrieve data
        ActiveCell.FormulaR1C1 = _
           "=SUMIFS("" & D & ""!R4C5:R100000C5,OTB!R4C2:R100000C5,"" & E         & ""!RC1)"

I did not include the full code as the rest is just repetition of what happens in the code that is here. and the end of the loop & Sub.

The error that pops up is application or object defined error.

Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Tim Stack, Jun 27 '19 at 10:35
What Tim said. You would benefit from using more descriptive variable names as well. — eirikdaude, Jun 27 '19 at 11:35

score 0 · Answer 1 · answered Jun 27 '19 at 11:02

When you join a folder path with a book name or with any other path inside this folder - you need to use Application.PathSeparator as an intermediate string between those. I advise you to use MsgBox(D) before assigning formulas to cells so you can check if your path is correct.

score 0 · Answer 2 · answered Jun 27 '19 at 11:02

I'd check the following lines:

Maybe D = A & "\" & B and E = A & "\" & C would help. (Application.Pathseparator is the correct choice instead of "\" but it's not a big issue.)

Maybe ...""!R1C1)" ... instead of ...""!RC1)"

Maybe there are too many quotes after SUMIFS. Consider this operation as a simple string operation for glueing parts. The result must be a syntatically correct command. I'd test it without leading equal sign and check if it is correct.

Inserting string into a formula in VBA

2 Answers2