RE: 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?

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?