Posts by mhabib

  • On Error Goto Statement not working consistently

    Hi,


    I've got the following code that's not working like I want it to.


    Code
    Sub TestProcedure()
    Dim MyNumber As Integer
    On Error GoTo 1 'Redisplay InputBox
1:
    MyNumber = 0 'Initialize variable
    MyNumber = InputBox("Enter an Integer between 1 and 20")
    MsgBox MyNumber
End Sub


    What I'd like is to redisplay the InputBox in case the user inputs a non-numeric value. This works fine on the first instance of non-numeric input - the input box is redisplayed. However, the second time a faulty value is entered, a run-time error appears:


    "Run-time error '13': Type mismatch"


    Any help would be much appreciated.


    m

  • Data Validation - Drop Box Values - Only If Active Customer

    Re: Data Validation - Drop Box Values - Only If Active Customer


    Here's a round about solution:


    1. Create a macro to dump Active Clients (using Advanced Filter) to another sheet. (This list can be used [dynamically] as the reference list for Data Validation.)
    2. Use the workbook Open event as well as Sheet1 Activation as triggers for firing macro in #1 above.


    Sample workbook attached for your reference.


    HTH


    m

  • VBA Sum Formula be modified?

    Re: VBA Sum Formula be modified?


    Haven't looked at your code in the last post, but here's a fix that seems to work for the problem you describle two posts ago.



    HTH


    m

  • VBA Sum Formula be modified?

    Re: VBA Sum Formula be modified?


    Try this code:



    HTH


    m

  • VBA Sum Formula be modified?

    Re: VBA Sum Formula be modified?


    How about modifying your If as follows?


    Code
    If r.Offset(, -3) = "Without Pattern Change" Then
            txt = Left(txt, Len(txt) - 1)
            r.Formula = "=sum(" & txt & ")": txt = ""
        ElseIf r.Offset(, 1) <> "S" And Not r.Offset(, -3) Like "*Pattern*" Then
            txt = txt & r.Address(0, 0) & ","
     [b]
Else
            txt = ""[/b]
        End If


    HTH


    m


    PS: Clever Code

  • SFAS 123 Calculations

    Re: SFAS 123 Calculations


    Hi Bob,


    Welcome to Ozgrid.


    The "@" sign in 1-2-3 is (was?) used to indicate that a function is being called. In your case it's the natural logarithm (ln). I would suggest that you simply drop the "@" sign from your formula(s) and use it in Excel.


    So this in 1-2-3:
    ((@ln(B1/B2)+(b4-B5)*B3)(b6*b3^.5))+.5*b6*b3^.5


    Should look like this in Excel:
    ((ln(B1/B2)+(b4-B5)*B3)(b6*b3^.5))+.5*b6*b3^.5


    HTH


    m

  • Display Add-in Macro names in the "Run Macro" dialog

    Re: Display Add-in Macro names in the &quot;Run Macro&quot; dialog


    Thanks Andy!


    Your method's better than what I'm doing now (getting into VBE and running code from there) - though it'll be difficult memorizing the names of the 10+ macros that I have in the add-in. I'll go ahead with the User Form idea. Will bug you if I'm stuck.


    Thanks once again to you and Jack in the UK for your time and guidance.


    Regards,


    m

  • Display Add-in Macro names in the "Run Macro" dialog

    Re: Display Add-in Macro names in the &quot;Run Macro&quot; dialog


    Hi Jack,


    The way I've been running my add-in macros (esp. those that are not assigned to custom buttons) is by getting into the VBE, placing my cursor within the desired code and clicking the run button. However, I just wanted to know if there was a way I could do it without getting into VBE. I guess there isn't.


    As an alternative, is it possible to create a user form with a combo box that lists all general module macros, so that I could use that as a way to run my add-in macros?


    Thanks.


    m


    Edit: Sorry, I posted without checking your reply. I'll give your add-in a try. Thanks.

  • % Variance

    Re: % Variance


    Hi jshayler,


    I see nothing wrong with either your formula, or the results that you're getting.


    If your base is 78, then a score of 82 represents 5.1% (roughly), which technically lies outside the 5% variance range.


    HTH.


    m

  • Display Add-in Macro names in the "Run Macro" dialog

    Re: Display Add-in Macro names in the &quot;Run Macro&quot; dialog


    I'm not having any problems with the functions.


    It's the Subs that I need help with. They're all General Module procedures.


    The Run Macro dialog shows a list of GM macros in all open workbooks - Except the Add-in.


    Thanks.


    m

  • Display Add-in Macro names in the "Run Macro" dialog

    Hi,


    I have an add-in (xla) file that contains some general-purpose macros and functions. I generally assign my macros to custom toolbar buttons.


    I've written some new macros in the add-in, and would like to be able to run them without using custom buttons. However, the Run Macro (Alt F8) dialog doesn't display the names of my macros. Is there a way for getting all of the macros in my add-in file to show up in the above mentioned dialog?


    Thanks.


    m

  • insert calculation in pivot

    Re: insert calculation in pivot


    Try this:


    Right-click any part of the Pivot Table Report that you already have, select Formulas and click Calculated Field.


    You'll find the rest of the steps self-evident.


    HTH


    m

  • Locate missing numbers in a large set

    Re: Locate missing numbers in a large set


    Hi Marcus,


    I think COUNTIF would be a better way to go, as you can do block searches instead of single-column (as in the case of VLOOKUP). Obviously, with only 65536 rows available, your used up numbers must reside in a range spanning at least several columns.


    Will need a snapshot of your data to give you more specific suggestion.


    HTH


    m