Posts by Om Avataar

    Re: Range(activecell, Activecell.end(xlright)).copy


    Thanks, Andy, for that clarification... But now I have a real problem - for my overall program, I need my code to be able to copy Cell A5 and ALL cells to the right of cell A5 right up to the last non-blank cell in that row - and there WILL be empty, possibly non-consecutive cells scattered in between cell A5 and the last non-blank cell in the row.


    Also, I need the code to just copy all these cells on an 'as is' basis - without deleting the empty cells in between.


    Pls help... Thanks

    1. What's the difference between:

    Code
    Worksheets("Sheet1").Range("A5").Select


    and

    Code
    Worksheets("Sheet1").Range("A5").Activate

    ?


    2. Can I use a Selection. method with an object 'selected' using .Activate in the preceding code line?


    3. Say I am currently on Sheet2, Cell A5. When I click the macro button, I want Excel to copy the value from Sheet1, Cell A5 and paste it into Sheet2, Cell A5. But I do NOT want the screen display to actually jump from Sheet2 to Sheet1, and then back to Sheet2. So do I need to use .Activate, or .Select, to copy the value of Sheet1, Cell A5?

    Dear VBA Gurus,


    They say a picture is worth a thousand words; I have attached a zipped Excel workbook with 2 worksheets - which I hope will be self-explanatory as to what I am trying to achieve via the VBA procedure/macro I need help in coding.


    If anyone can help, that'd be awesome; I'm hoping my VBA code objective here will be a walk in the park for most of you Gurus out there.


    Many gazillion thanks in advance to anyone who can assist with the VBA code to 'macrofy' this. Just in case, my email address is [email protected]


    Cheers,
    - Om Avataar

    Dear VBA Gurus,


    In my Excel workbook, I have a main/index sheet (worksheet 'Main') which essentially acts as a Navigation sheet for the entire workbook. The workbook has about 40 or so sheets, which can be categorized into 6 groups. I have created a click button for each of these 6 groups - and clicking any button displays a combo-box listing of the names of worksheets belonging to that group. For example, if a user selects 'ABC' from this combo-box list, he will be brought to worksheet 'ABC' (the click button macro should also scroll to cell A1 of worksheet 'ABC'), and so on. There is a also a 'Cancel' option in each combo-box list should a user wish to not select any worksheet in that group.


    I have coded function insertCboPerfMgmt() below, which is called upon clicking button PerfMgmt_NavButton(). Although the combo-box object appears and is populated as expected - that is about all that goes right.
    Two main problems I am facing are: The macro does NOT take me to the worksheet selected in the combo-box, and the combo-box object does not disappear or get 'destroyed' after the user has made his selection.


    Plse advise what I am doing wrong... Hope I've described the problems in detail. Also do not assume any VBA knowledge on my part; I'm a total, absolute beginner!! Thanks a million in advance.


    Code
    Sub insertCboPerfMgmt()
        Set oleX = ActiveSheet
         
        oleX.Shapes.AddOLEObject(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=125, Top:=200, Width:=180, Height:= _
        16).Select
         
        Selection.ListFillRange = "AA9:AA15"
    End Sub


    Dear VBA Gurus,


    I have a large Excel workbook with close to 44 or so worksheets. These worksheets can be roughly grouped into 6 Main groups, i.e. {A1,A2,A3...A7},{B1,B2,B3...B7,B8,B9}, etc. On the main Start or Index tab, I have placed 6 buttons for each of the Main groups.


    Is there a way to code the buttons so that on clicking a Main button, say Button A, a drop-down menu opens to its right with navigation options to go directly to any tab/worksheet within that group, i.e. for {A1,A2,A3...A7}, the drop-down will have seven items, etc.


    Thanks.

    ...and exporting results from Visual C++ 2005 (VC++) to Excel. How does one do this? I want to use VC++ as the main calculation/simulation engine, as it is rumored to execute code ~15x faster than an equivalent algorithm coded/executed in Excel VBA.


    I'm not too sure about the interfacing part though, i.e. importing input data from Excel to VC++, and subsequently re-exporting results from VC++ back to Excel. Would anyone have sample code showing how such interfacing is done? I think I should be able to extend/customize your sample interfacing code for my specific needs.


    A million thanks in advance... and apologies if this question concerning VC++ does not belong here; I wasn't sure of where else to turn to for help.


    - om

    I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA):


    [FONT="Palatino Linotype"]Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class[/FONT]


    Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.


    Pls advise soonest possible. Many thanks in advance...

    I get the following cryptic error message:


    "Run-time error '438': Object doesn't support this property or method"


    when I try to run the following seemingly innocent code in Excel VBA:


    Code
    j = Application.WorksheetFunction.NormSInv(Application.WorksheetFunction.Rand())


    What am I doing wrong here?


    By the way, the following code

    Code
    j = Application.WorksheetFunction.NormSInv(Rnd(Now()))

    doesn't generate any error message, altho I'm not very sure if it does in fact generate random standard normal numbers as required.


    Any advice would be appreciated... Thanks.

    Hi VBA Gurus,


    The following code deletes the entire range from current row to row 2500:

    Code
    Rows(ActiveCell.Row & ":2500").Delete


    But I need for only Columns A-G in the above range of rows (i.e. current row to row 2500) to be 'deleted' or cleared of their contents (is there a 'ClearContents' or similar function in Excel VBA?); columns H onwards of current row to row 2500 I still absolutely need!


    To what should the code above be changed in order to achieve my objective? Is something like the following valid Excel VBA code??
    Rows(ActiveCell.Row & ":2500").Columns("A:G").ClearContents


    Pls advise... Thanks a million in advance.


    Cheers!
    - Om