Bruce,

So if I want to use the input value in the macro, do I just enter "instring"
in the spot where i want the value to appear.

The input value is going to be a year, so:
ActiveCell.FormulaR1C1 = "='2006'!R[-2]C[-1]-'2005'!R[-2]C[-1]"
Range("D6").Select

would now read

ActiveCell.FormulaR1C1 = "='instring'!R[-2]C[-1]-'2005'!R[-2]C[-1]"
Range("D6").Select

?

Thanks a lot.

-Sean


"bpeltzer" wrote:

Sub test()
Dim instring
instring = InputBox("What's the input?")
MsgBox ("You entered " & instring)
End Sub

Depending on your intended use, you'll probably add some tests to validate
the input for type, range, etc.
--Bruce

"Sean H" wrote:

Hi,

I am trying to write a macro that will be used to update a file. I want to
have some sort of stored input like you can in C++.

something like
input "what year is it?"
input a

...or however the C code goes (its been a couple years).

Is there any way to do this for an excel macro?

You need to create the formula string to look just as if you had hard-coded
it, that is, you need to get VBA to evaluate instring. So I think it would
be
ActiveCell.FormulaR1C1 = "='" & instring & "'!R[-2]C[-1]-'2005'!R[-2]C[-1]"
(After the = is an apostrophe followed by a quote; before the exclamation
is a quote followed by an apostrophe).
And you would probably first perform a test to ensure that there's a
worksheet whose name matches the input.

"Sean H" wrote:

Bruce,

So if I want to use the input value in the macro, do I just enter "instring"
in the spot where i want the value to appear.

The input value is going to be a year, so:
ActiveCell.FormulaR1C1 = "='2006'!R[-2]C[-1]-'2005'!R[-2]C[-1]"
Range("D6").Select

would now read

ActiveCell.FormulaR1C1 = "='instring'!R[-2]C[-1]-'2005'!R[-2]C[-1]"
Range("D6").Select

?

Thanks a lot.

-Sean


"bpeltzer" wrote:

Sub test()
Dim instring
instring = InputBox("What's the input?")
MsgBox ("You entered " & instring)
End Sub

Depending on your intended use, you'll probably add some tests to validate
the input for type, range, etc.
--Bruce

"Sean H" wrote:

Hi,

I am trying to write a macro that will be used to update a file. I want to
have some sort of stored input like you can in C++.

something like
input "what year is it?"
input a

...or however the C code goes (its been a couple years).

Is there any way to do this for an excel macro?