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

[chaju]

help!!

[Colo]

Hi chaju!

'Please copy both of codes into a standard module.

Sub InputsFormula()
Dim sh(0 To 1) As Worksheet, cnt As Integer, lngRow As Long

Set sh(0) = Sheets("Sheet1") 'change to a sheet name to input Formula
Set sh(1) = Sheets("Sheet2") 'same as above

lngRow = Selection.Row

For cnt = 0 To 1
With sh(cnt)
.Cells(lngRow, 3).Formula = _
"'=C:\\test\\[Valuation " & .Cells(lngRow, 1).Text & ".xls]Ingenium'!$E$11"
End With
Next
End Sub

'Please active the sheet with which you want to make a button,
'and execute "Sub MakeBottonOnActiveSheet".

Sub MakeBottonOnActiveSheet()
Dim rngBt As Range, objBt As Button
Set rngBt = Application.InputBox _
("Select range where you want to make Button", Type:=8)
With rngBt
Set objBt = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With
With objBt
.OnAction = "InputsFormula"
.Text = "Click"
End With
End Sub

[Colo]

Please reduce the number of "/" in the formula.
It seems that the character became strange Because, my Excel is a Japanese version.
Regards,

[chaju]

Thanks very much! but I find a mistake in my content..

In the 4th paragraph I have:
"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 "

it should be:
"Can I have a Macro button so that it inputs (when pressed) this formula into Cell C1 (1st Sheet): =C:test[Valuation 25-Mar-02.xls]Ingenium'!$E$11, and input this formula into Cell C1 (2nd Sheet): =C:test[Valuation 25-Mar-02.xls]Ingenium'!$E$4"

Sorry about that,, But I guess I should can the code much..

So, I look forward to see the amended code..

Many thanks

[Colo]

Hello again, the amended code is following...


Sub InputsFormula()
Dim lngRow As Long
lngRow = Selection.Row
With Sheets("Sheet1")
.Cells(lngRow, 3).Formula = _
"=C://test//[Valuation " & .Cells(lngRow, 1).Text & ".xls]Ingenium'!$E$11"
End With
With Sheets("Sheet2")
.Cells(lngRow, 3).Formula = _
"=C://test//[Valuation " & .Cells(lngRow, 1).Text & ".xls]Ingenium'!$E$4"
End With
End Sub

[Colo]

The characters "//" in above code mean "".
Thanks,

[chaju]

Thanks I have the code now.. But it doesn't work ... I think is the sheet name problem.

Suppose My 1st Sheet have name MonMkt
Is it right that the code should have:

With Sheets("MonMkt") instead of the suggested:
With Sheets("Sheet1")
That's what I have,, but not working.. pls advise.. sorry to bother again.

[Colo]

Hi.
Does any error message come out?
I think, how to write sheet name is no problem as you wrote.
You should select "Row" before click button.
If you want to formula into Cell C1 then please select any cells in row1.
Do I make myself clear? Sorry, I am not an English native.
Regards,

[chaju]

Thanks Colo,, I have modified the code you gave me, now my sheets have 10sheets, only 8 sheets are needed to perform action with that button. You can see the 8 sheets name below. After editing the code, I press F5 (in VB) (F5 means Run Sub/UserForm) and error message pop up say: "RUN time error '9': Subscript out of range"

Can you pls check my modified code and help me please........ please, we are nearly there!! Code as follow:

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



Sub MakeBottonOnActiveSheet()
Dim rngBt As Range, objBt As Button
Set rngBt = Application.InputBox _
("Select range where you want to make Button", Type:=8)
With rngBt
Set objBt = ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height)
End With
With objBt
.OnAction = "InputsFormula"
.Text = "Click"
End With
End Sub