Addition string from input box in to a formula (eg;R1C1 Formula)

[Guest]

=>I have a file name in the input box which will be entered by the user.
=>How can we call this variable into a formula.
=>for eg: I want to use the file name into a vlook up formula for getting
some information from a different workbook. However, the formula for Vlookup
is got by using Activecell.formular1c1 which allows us to get the formula by
inputting the whole fomula with in codes. Hence, when I call for the variable
file name in the middle of this formula its not recognising.

Need help at the earliest.

Rgds,

Dev,

 

[smithb2]

similar issue

Dev,

I have a similar issue.

I have an integer stored in a variable that I want to insert into a R1C1 formula.

The Compiler for VBA has no problem with this, however, there is an error in run-time, Application or user defined error.

If you hear anything Please let me know and I will do the same.

Cheers,
Smithb2
(e-mail address removed)

 

[smithb2]

Solution!!

As promised, Here is the solution I found to my dilemma:

As you may recall, as with your situation, I was trying to automatically determine, with a macro, how many columns were to be added and insert this info into the sum formula:

Option Explicit
Dim i As Integer

Sub HrsTotal()
i = 0

‘Do loop determines # of columns to store in integer variable (Hrs is the header for the ‘column

Do While ActiveCell.Value <> "Hrs"

ActiveCell.Offset(-1, 0).Select

i = i + 1

Loop

‘Following line uses the # stored in “i” to select cell in which to place sum function

ActiveCell.Offset(Int(i), 0).Select

‘In the following, I simply turn the value stored in “i” from positive to negative, in ‘preparation for sum formula.

i = -(i)

ActiveCell.FormulaR1C1 = "=SUM(R[" & i & "]C:R[-1]C)"

End Sub


The last line contains what I was unable to figure out, How to get the sum formula to accept the variable "i" as a value: " & i & "

Have a great day,
Smithb2