Input for Macros
 

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?

Input for Macros
 

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?