user input and concatenate

[Brad]

I have the following macro where I:

1) select a sheet (Sheets("Open 135").Select)
2) jump to a cell I know falls within the array formula area
(Range("J85").Select)
3) modify the array formula (Selection.FormulaArray =
"=QLink|Bars!'TXN,135,100,DTOHLCV,HEADERS,FILL')
4) then, I am jumping to a second sheet and modifying the array formula
again...

My question regards my needing to add code to input a particular portion of
the array formula using the INPUTBOX Function
so that I concatenate my user provided input within the code in 3) above.

I would like to have Selection.FormulaArray = followed by:

"=QLink|Bars!'

then my symbol input (TXN in the above example)

then finish up with

,135,100,DTOHLCV,HEADERS,FILL'"

to complete the line of code.

So, code for an INPUTBOX sticking the user-provided input between the two
pieces of text above.

If I do this correctly, I may substitute any number of symbols for stocks or
indices in place of the TXN.

Here's the current macro below... my thanks in advance for any help here.

Cheers! Brad

Sub Macro135()
'
' Macro135 Macro
' Macro recorded 3/21/2005 by BR
'

'
Sheets("Open 135").Select
Range("J85").Select
Selection.FormulaArray =
"=QLink|Bars!'TXN,135,100,DTOHLCV,HEADERS,FILL'"
Range("J106").Select
Sheets("Open Day").Select
Range("F86").Select
Selection.FormulaArray = "=QLink|Bars!'TXN,D,100,DTOHLCV,HEADERS,FILL'"
Range("I105").Select
End Sub

[Toppers]

Brad,
like this ?

Sub Macro135()
'
' Macro135 Macro
' Macro recorded 3/21/2005 by BR
'

'
MyData = InputBox("Enter Data") ' e.g. TXN

Sheets("Sheet1").Select

Range("J85").Select
Selection.FormulaArray = "=QLink|Bars!'" & MyData &
",135,100,DTOHLCV,HEADERS,FILL'"
Range("J106").Select
Sheets("Sheet2").Select
Range("F86").Select
Selection.FormulaArray = "=QLink|Bars!'" & MyData &
",D,100,DTOHLCV,HEADERS,FILL'"
Range("I105").Select
End Sub


HTH
"Brad" wrote:

> I have the following macro where I:
>
> 1) select a sheet (Sheets("Open 135").Select)
> 2) jump to a cell I know falls within the array formula area
> (Range("J85").Select)
> 3) modify the array formula (Selection.FormulaArray =
> "=QLink|Bars!'TXN,135,100,DTOHLCV,HEADERS,FILL')
> 4) then, I am jumping to a second sheet and modifying the array formula
> again...
>
> My question regards my needing to add code to input a particular portion of
> the array formula using the INPUTBOX Function
> so that I concatenate my user provided input within the code in 3) above.
>
> I would like to have Selection.FormulaArray = followed by:
>
> "=QLink|Bars!'
>
> then my symbol input (TXN in the above example)
>
> then finish up with
>
> ,135,100,DTOHLCV,HEADERS,FILL'"
>
> to complete the line of code.
>
> So, code for an INPUTBOX sticking the user-provided input between the two
> pieces of text above.
>
> If I do this correctly, I may substitute any number of symbols for stocks or
> indices in place of the TXN.
>
> Here's the current macro below... my thanks in advance for any help here.
>
> Cheers! Brad
>
> Sub Macro135()
> '
> ' Macro135 Macro
> ' Macro recorded 3/21/2005 by BR
> '
>
> '
> Sheets("Open 135").Select
> Range("J85").Select
> Selection.FormulaArray =
> "=QLink|Bars!'TXN,135,100,DTOHLCV,HEADERS,FILL'"
> Range("J106").Select
> Sheets("Open Day").Select
> Range("F86").Select
> Selection.FormulaArray = "=QLink|Bars!'TXN,D,100,DTOHLCV,HEADERS,FILL'"
> Range("I105").Select
> End Sub
>
>
>