Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: macro needed!

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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




  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    help!!

  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

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

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #9
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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,

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •