macro needed!

[chaju]

Thanks to those who take a look at this and help!

Suppose the file consists of 2 worksheets

For the both sheets:
Cell A1 have date: 25-Mar-02

Can I have a Macro button so that it inputs (when pressed) this formula into Cell C1 (BOTH Sheets): =C:\test\[Valuation 25-Mar-02.xls]Ingenium'!$E$11

Note: the date within the file name corresponds to the date in Cell A1 for both sheet.

Suppose both sheet's Column A has already been filled with date. The next step I wish the macro to do is whenever I press the button it will fill the formula mention above into C2 (for both sheets). I must ensure Cell C2 formula (in both sheets)have the file name with the date correspond to their sheet's Cell A2's date.

when I press the button it will do the same to cell C3 with both formula reading the dates in cell A3 (for both sheets)

As I am very new to macro Please asist step by step of how to create a macro button and where to put the VB code...

Many Many Many thanks to the helper,,,
Deeply appreciated

 

[Colo]

Hi. I am not able to test it , but please try following code. (I summarized your code short.)
Regards,

Sub InputsFormula()
Dim lngRow As Long, arrSh, arrRng, intCnt As Integer
arrSh = Array("MonMkt", "GEqty", "GBond", "USEqty", "USBond", _
"EuEqty", "AsianEqty", "HKEqty")
arrRng = Array(4, 4, 11, 6, 9, 5, 10, 8, 7)
lngRow = Selection.Row
For intCnt = LBound(arrSh) To UBound(arrSh)
With Sheets(arrSh(intCnt))
.Cells(lngRow, 5).Formula = _
"=I:VULYEAR 20022nd QtrValuation[Fortune Valuation " _
& .Cells(lngRow, 1).Text & ".xls]Ingenium'!$E$" & arrRng(intCnt)
End With
Next
End Sub

 

[chaju]

You Mention:
arrRng = Array(4, 4, 11, 6, 9, 5, 10, 8, 7)
is
arrRng = Array(4, 11, 6, 9, 5, 10, 8, 7) that correct? I wonder!!

Anyway, the macro works upto creating the button and stop with message popup saying (Subscript out of range) and start debugging at this line in the code:

"With Sheets(arrSh(intCnt))"

Where has the problem come from?

Thanks very much

 

[Colo]

>arrRng = Array(4, 11, 6, 9, 5, 10, 8, 7) that correct?
Above is correct. Sorry That was my miss typed.

Sorry, I cannot find out the factor in question.
If you are able to send mail to me, I would send sample xls file to you.

Regards,

 

[chaju]

Yes pls send me the sample file to Julian.Chan@sunlife.com.hk or to julian@popmail.com

I appreciate you great help..

 

[Colo]

Oops! I could Find my miss type!
"'" of Beginning of a formula was nothing.
Please try after change to following formula.

And I'll email to you later.

regards,

 

[chaju]

Hi again, I don't understand what you meant by "beggining is nothing" why did you have "" in you text?

So where should I correct with the code?

Speak to you later

 

[Colo]

Sorry, I have too many mistypes.
>"'" of Beginning of a formula was nothing.
I wanted to type ' = Apostrophe

The formula is...

"='I:VULYEAR 20022nd QtrValuation[Fortune Valuation " _
& .Cells(lngRow, 1).Text & ".xls]Ingenium'!$E$" & arrRng(intCnt)

I've sent email to you.

 

[chaju]

thanks.. i am still waiting for the mail.. i know you have send it... I hope you have send to the popmail address... the sunlife one is quite slow to pick up new mails..

 

[Colo]

Hi. I've sent same mail to your popmail address now.
And I can not access this site until day after tomorrow because of my job.
So, If the problem will not be solved, someone help us please.
With thanks,