Posts by farful

    I create a dynamic array. I want to output all the values in my dynamic array in separate rows.


    Code
    MsgBox ("the values of my dynamic array are: " & vbCr & _
    myarray(1) & vbCr & _
    myarray(2) & vbCr & _
    myarray(3) & vbCr & _ 
    ....
    myarray(i))


    something like that... how would i do this?

    I'm currently using WeekdayName in VBA to return the name of day of the week in English.


    I transfer this macro to another computer that uses an excel/OS that is in another locale/language. When I run it there, WeekdayName produces the name of the week in that language. How do I have WeekdayName always return the name of the day of the week in English?

    Suppose I have on a sheet, on say cell "J10" a formula "=A1"


    Now suppose I to delete row 1, Then J9, which was formally J10, will now say "=#REF!" instead of "=A1"


    Is there a way to maintain J9 (which was J10) to say "=A1" without giving a reference error?

    Re: Add One Month To Date


    I suppose that is part of my problem, defining "exactly one month"
    In general, if I start on any given day, then the "month" will last until that day - 1.
    So if i start on 1/13 then the end of that "month" will be 2/12.


    However, if i started say on 1/30 then I want the "months" to be as followed:
    1/30-2/27 (assuming no leap year), 2/28-3/29, 3/30-4/29, etc.
    If i started on 1/31, then:
    1/31-2/27, 2/28-3/30, 3/31-4/29, 4/30-5/30, etc.


    I assume this really doesn't make much sense... so I just ended up coding lots of if/then statements to get all the exceptions cases the way i needed it to be. It actually wasn't as much as I thought it would be... but if there is some built in function that does this (assuming people could actually follow my logic on what a "month" is o.O) i'd still be curious to know!


    Formula by peanut gives 3/1 when adding 1 month minus 1 day to 1/31.
    RichardSchollar - I don't have "atpvbaen.xls (ie the Analysis Toolpak)" where is that from?

    Ok I think this is a really silly problem... Is there a way to add 1 month minus 1 day to a given date? With the exceptions below:


    Let d m and y be the day month and year respectively.



    Is there a simpler way to do this? Or perhaps some built in function?

    Re: Insert Multiple Data With Lookup Called From Drop-down Box


    Yes, it's possible with VBA. You want something liket his in your sheet:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$K$6" Then
            Range("H3").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= _
                    "=stuff"
            End If
    end sub


    You can do this for H3 L3 and P3. You most likely want to add a default value and delete validations before adding them (as it will give you an error if you try to add if one already exists)

    Re: Importing Lines From Large Text File


    I'm not sure how you're importing it, but you should be able to use this to get it working for you:

    I was wondering if the following was possible:


    Suppose I have multiple workbooks, say Workbook1 and Workbook 2. They each have a macro named MyMacro stored in their workbooks, and they do different things.


    I want to create ONE button, by rightclicking the toolbar, and going into customize. There I can assign a macro, say "Workbook1.xls!MyMacro.MyMacro" or "Workbook2.xls!MyMacro.MyMacro" Is it possible to default the workbook name to whatever workbook you have currently active? such as "ActiveWorkbook.xls!MyMacro.MyMacro" or anything like that?


    So if I click on the button whilst having Workbook1 active, it'll run MyMacro from Workbook1, and if Workbook2 was active, and I hit the button, then it'll run MyMacro from Workbook2.

    Re: Macro To Sum Column


    Ok I think I finally got it...

    Re: Macro To Sum Column


    Okay, this is my assumption on what your excel sheet will look like. You have data from rows 1 to 7, and now you want to use your macro. So you select A8 (since it's the next blank row) and run your macro. Then you continue filling up this worksheet starting from row 9 and continue till say row 19. Then you select A20 and use your macro again. So on and so forth.


    If this is correct, I'd like to make a suggestion. Using my example, I suggest you leave a blank row after every highlighted/summed row. It's easier for a macro to handle this and it looks better! (in my opinion). So what I mean by this is after row 8 is your highlighted/summed row, skip blank 9 then start inputing your data again start from row 10.


    If you leave a blank row, the following code SHOULD work. If you really do NOT want to separate these groups with a blank row, let me know, and I'll make something for that...


    Re: Macro To Sum Column


    I apologize for not being able to understand your question. Try the following code below



    The last two lines with the sum is still probably not you want. I'm not sure I understand....
    Let's use your example of: A1:D7 is category 1 with row 8 containing a total in column C and D, A9:D14 is category 2. So let's say you start the macro with A15 selected. (stop me if I'm already wrong). Do you want it to sum ONLY from C9:C14? (with the sum answer being on C15)