Macro Button needed for this function

chaju · Mar 26th, 2002, 11:02 PM

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

[ This Message was edited by: chaju on 2002-03-27 00:03 ]

[ This Message was edited by: chaju on 2002-03-27 01:45 ]

chaju · Mar 27th, 2002, 12:52 AM

Any one can help me with this??

Thanks for you attention

Tom Schreiner · Mar 27th, 2002, 02:30 AM

Hi chaju
I do not understand exactly waht it is you are trying to do?
Please repost with more details...
Thanks
Tom

chaju · Mar 27th, 2002, 03:06 AM

I have edit the content of the problem.. see if you can understand it this time.. thanks

Tom Schreiner · Mar 27th, 2002, 03:23 AM

Sorry, I
still do not understand.
Why not just copy and paste doen column C?
I hope someone else can help you.
Tom

Tommy Bak · Mar 27th, 2002, 04:48 AM

Run this macro when stading in the cell with the date. The cell next to it will then reference to the actual valuationworkbook

Sub test100()
Dim sBook, sFilePath As String
sBook = "[Valuation " & ActiveCell.Text & ".xls]Ingenium'!$E$11"
sFilePath = "C:test"
ActiveCell.Offset(0, 1).Value = "='" & sFilePath & sBook
End Sub

Regards Tommy

Tommy Bak · Mar 27th, 2002, 05:12 AM

Hi
Disregard my last post. I did post a temporary sub.
This one, I hope, will do what you ask for.

Sub test101()
Dim sBook, sFilePath As String
R = ActiveCell.Row
sBook = "[Valuation " & Cells(R, 1).Value & ".xls]Ingenium'!$E$11"
sFilePath = "C:test"
Sheets(1).Cells(R, 3).Formula = "='" & sFilePath & sBook
Sheets(2).Cells(R, 3).Formula = "='" & sFilePath & sBook
ActiveCell.Offset(1, 0).Select
End Sub

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 11:02 PM	#1
chaju Board Regular Join Date: Mar 2002 Posts: 82	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 [ This Message was edited by: chaju on 2002-03-27 00:03 ] [ This Message was edited by: chaju on 2002-03-27 01:45 ]

Mar 27th, 2002, 12:52 AM	#2
chaju Board Regular Join Date: Mar 2002 Posts: 82	Any one can help me with this?? Thanks for you attention

Mar 27th, 2002, 02:30 AM	#3
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi chaju I do not understand exactly waht it is you are trying to do? Please repost with more details... Thanks Tom

Mar 27th, 2002, 03:06 AM	#4
chaju Board Regular Join Date: Mar 2002 Posts: 82	I have edit the content of the problem.. see if you can understand it this time.. thanks

Mar 27th, 2002, 03:23 AM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Sorry, I still do not understand. Why not just copy and paste doen column C? I hope someone else can help you. Tom

Mar 27th, 2002, 04:48 AM	#6
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Run this macro when stading in the cell with the date. The cell next to it will then reference to the actual valuationworkbook Sub test100() Dim sBook, sFilePath As String sBook = "[Valuation " & ActiveCell.Text & ".xls]Ingenium'!$E$11" sFilePath = "C:test" ActiveCell.Offset(0, 1).Value = "='" & sFilePath & sBook End Sub Regards Tommy

Mar 27th, 2002, 05:12 AM	#7
Tommy Bak Board Regular Join Date: Feb 2002 Location: Guderup, Denmark Posts: 287	Hi Disregard my last post. I did post a temporary sub. This one, I hope, will do what you ask for. Sub test101() Dim sBook, sFilePath As String R = ActiveCell.Row sBook = "[Valuation " & Cells(R, 1).Value & ".xls]Ingenium'!$E$11" sFilePath = "C:test" Sheets(1).Cells(R, 3).Formula = "='" & sFilePath & sBook Sheets(2).Cells(R, 3).Formula = "='" & sFilePath & sBook ActiveCell.Offset(1, 0).Select End Sub