VBA formula with variables

[greyangel]

indexfilepullinG1 = Right(Range("C6").Formula, 5)
'(This pulls a last 5 digits of the formula ie: $C$9)


inventoryWkBk= "inventory by Area of Resp May 2017.xlsx

'(Achieved by using a vba created function that pulls everything to the right of the \)(This is located in cell A3)

inventoryfilepath= "C:\Users\Jdoe\documents\working on macro

'(Achieved by using Left(A1,find(A3,A1)-2)

'Finally this is the part in which it bombs out.

Range("C6").Formula = "='" & inventoryfilepath & "\" & "[" & inventoryWkBk & "]Indexcalculation8.0'!" & indexfilepullinG1 & ""



I get an error message

[greyangel]

Can anybody tell me what I am doing wrong here?

[shrivallabha]

In this line:

inventoryWkBk= "inventory by Area of Resp May 2017.xlsx
I do not see closing quote

inventoryWkBk= "inventory by Area of Resp May 2017.xlsx"

[greyangel]

indexfilepullinG1 = Right(Range("C6").Formula, 5)
'(This pulls a last 5 digits of the formula ie: $C$9)


inventoryWkBk= "inventory by Area of Resp May 2017.xlsx"

'(Achieved by using a vba created function that pulls everything to the right of the \)(This is located in cell A3)

inventoryfilepath= "C:\Users\Jdoe\documents\working on macro"

'(Achieved by using Left(A1,find(A3,A1)-2)

'Finally this is the part in which it bombs out.

Range("C6").Formula = "='" & inventoryfilepath & "\" & "[" & inventoryWkBk & "]Indexcalculation8.0'!" & indexfilepullinG1 & ""



Yeah sorry about that in the real code the "inventoryfilepath" and the "InventoryWkBk" are variables that uses a formula to separate certain pieces of a file path in a particular cell so they can be used by a formula in another workbook.

[mana]

I guess

>indexfilepullinG1 = Right(Range("C6").Formula, 5)

indexfilepullinG1 = mid(Range("C6").Formula, 2)

or

indexfilepullinG1 = Range("C6").DirectPrecedents.Address

[shrivallabha]

Just print the formula to the immediate window by modifying the code a bit

indexfilepullinG1 = Right(Range("C6").Formula, 5)
'(This pulls a last 5 digits of the formula ie: $C$9)


inventoryWkBk= "inventory by Area of Resp May 2017.xlsx"

'(Achieved by using a vba created function that pulls everything to the right of the \)(This is located in cell A3)

inventoryfilepath= "C:\Users\Jdoe\documents\working on macro"

'(Achieved by using Left(A1,find(A3,A1)-2)

'Verify the formula getting built up
strFormula = "='" & inventoryfilepath & "\" & "[" & inventoryWkBk & "]Indexcalculation8.0'!" & indexfilepullinG1 & ""
Debug.Print strFormula
'Finally this is the part in which it bombs out.
Range("C6").Formula = strFormula

[greyangel]

Just print the formula to the immediate window by modifying the code a bit

indexfilepullinG1 = Right(Range("C6").Formula, 5)
'(This pulls a last 5 digits of the formula ie: $C$9)


inventoryWkBk= "inventory by Area of Resp May 2017.xlsx"

'(Achieved by using a vba created function that pulls everything to the right of the \)(This is located in cell A3)

inventoryfilepath= "C:\Users\Jdoe\documents\working on macro"

'(Achieved by using Left(A1,find(A3,A1)-2)

'Verify the formula getting built up
strFormula = "='" & inventoryfilepath & "\" & "[" & inventoryWkBk & "]Indexcalculation8.0'!" & indexfilepullinG1 & ""
Debug.Print strFormula
'Finally this is the part in which it bombs out.
Range("C6").Formula = strFormula


Well the code is working now. Thank you for all of your help.