Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: macro needed!

  1. #11
    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. 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

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

    Default

    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

  3. #13
    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

    >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,

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

    Default

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

    I appreciate you great help..

  5. #15
    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

    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,

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

    Default

    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

  7. #17
    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

    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.

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

    Default

    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..


  9. #19
    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. 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,

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
  •