Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with macros/functions

    Hi,

    I have a user-defined function and a macro (sub-program) which I have created in my spreadsheet. However, there seems to be a conflict between the two. When I use the function to get a specific value and then I try to call on the macro, nothing happens in my sheet.

    Also, for my function I get "#VALUE" error which disappears the moment I press F9 to update the calculations. When I delete the cell that contains the function and I run the macro, I get the correct execution from my macro.

    Where is this conflict? How can I resolve it?

    Thanks, Hanan.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Could you possibly upload a workbook containg both the macro and UDF, as without viewing the code involved it is not possible to say what the problem might be.


    Andrew C

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Andrew, here is a sample SS I am working on...

    Hanan.
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Hi Andrew,

    Here is a revised workbook. The #REF in the previous workbook were reference to deleted worksheets. However, they do not/should not interfere with the function or the macro. Could you please have this workbook check?

    Thanks, Hanan.
    Attached Files Attached Files

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Hanan, can I ask one more question. The Macro coipies the current selection to H5. What is usually th erange you are copying, especially when you get problems ?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Hanan, I really don't know what is going on. When your macro is run, for some reason the values in cells d1:d3 evaluate to a #VALUE error, but correct themselves. So it should not matter unless they are the cells you are copying. Now for an interesting bit. I have two workbooks with identical function and macro. Yet in one when I copy D13 I get the right answer, in the other (the one you posted) I get #VALUE errors. I wish I knew what I done. I am posting the one that seems to work.

    I think you may have the colorindex arguments in D2 and D3 mixed up so I changed them (That was not part of the problem). I also removed the unnecessary workbook change event macro from the module as it does nothing. (It should be in the Workbook object, but you do not need it).

    Let us know how you get on,

    Andrew
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Hi Andrew,

    I tried the attachment that you have posted. However, I keep getting the same problem as with my workbook. By the way, I am using Excel '97.

    The range that I copy and paste into H5 is usually A6120. It does not include the UDFs in D13. The option of using COUNTIF will not work best for me since the I may not have the same name in the green cells all the time.

    Is there another option of counting I can use that you can think about? It does not have to be green cell, but maybe just counting the number of times "Sample" is being used in column A.

    Thanks, Hanan.

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Hanan,

    I did try with with XL97 and it does not work as well as with 2000. So if you try<pre> <big>=COUNTIF(A6:A177,"Sample*")<big></pre>

    in D1 and the equivalent in D2 and D3 you should be Ok. If you have no other data in column A, you could change it to =COUNTIF(A:A,"Sample*") so that if you add more rows the formula will work without changing it. The same for the other columns.

    Hope that gets you back on track,

    Andrew

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    Thanks Andrew. The COUNTIF function will work great for me. I essentially wanted to count the number of time a certain name appears in a column. The coloured cells were more for visualization purposes. Thanks again for all your help.

    Hanan.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Problem with macros/functions

    I'm glad you got sorted at last. The count colors is not a great idea as Excel does not re-calculate as a result of format changes. However I don't know what caused the problems you experienced.


    Andrew

Posting Permissions

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