Posts by miko68

    I'm trying to duplicate the Autosum button in Excel by using VBA, specifically the part where it only adds columns that have values in them. You know, when it stops if there's a blank cell above.

    You see, I have a summary sheet with different groups classifying the employees. In each group, there must be a total salary for that group. So as to not include the total of a group to the total of another group, I've put a blank row between each groups. That's where my problem is.

    Oh, and in case you're wondering why don't I just use the autosum function, well I'm trying to make it look like a program as much as I can, meaning The user only needs to input data and not do any calculations like sorting, column width, autosum, etc.

    I feel so stupid! I found the answer by searching here more thoroughly.
    I hope someone deletes this useless thread

    Re: Cell Address Of Lookup Result

    didn't see that Dave posted in. Anyway to answer his question, well it's not that I don't want to use it, it's just that in times like this I tend to take the last suggestion then work my way up to other suggestions if it didn't work. It's just me. But I tried that one too and they're just the same right? So it shouldn't be a problem which one I use.

    Re: Find Cell Address Of Lookup Result

    Thanks h1h! Your code can at least give the row number, and I can pretty much work on the rest. I just hope it won't slow down when there are about a hundred names in the report, although that's quite unlikely

    Is it possible to get the location of the result cell of a lookup result. For example, instead of showing the cell value it shows the cell address of the results of a lookup.

    You see I have this Summary Report of a payroll system. Everyone doesn't need to pay social charges, except two people, "person1" and "person2". What I want to do is to first look at the names in the report to see if one of those people are listed in the summary(this can be done by lookup, I think). Then go to a different column on the same row as the person.
    So, for example, "person1" is in a14, then the macro will select the cell say... g14.


    Re: Array Formula In Worksheetfunction?

    Well I use that large range because I want to cover all cells since the database will get bigger, and I know I should use dynamic ranges but I just can't work them for the life of me(T_T).

    As for the number of array formula I have, I have 3.One for the Empno, which take the lastname and firstname as criteria; periodcovered, which takes projectname and empno as criteria; and basic rate, which also takes the projectname and empno as criteria.
    Although all of those follow the same formula I've written above with only changing the references.

    Anyway, thx for that tip about reactivating the calculation.

    Re: Array Formula In Worksheetfunction?

    i'm not trying to decrease the size of my workbook, I just want to make the load times faster. You see I found out that when my calculation is set to automatic, it takes practically forever to get the values. It even takes a while to load just opening the worksheet. Then I set the calculation to manual(by options of course), and it took mere seconds to open my worksheet, only problem is there's no value. So I was thinking maybe I can speed the load times if entered the formula "programatically", so I never have to set calculation to automatic.
    But then you said that the speed doesn't change in terms of calculation, so I guess this won't work.

    Re: Array Formula In Worksheetfunction?

    I got a run-time error saying:
    Unable to set the FormulaArray property of the Range class
    Here's the code where the error occurs, where I had just copy/pasted the formula in the cell to the code.

    Range("c5").FormulaArray = "=IF(AND(I28<>"",J28<>"",K28<>""),INDEX('[LIST OF CONSULTANTS NEW.xls]Sheet1'!$A$5:$A$3947,MATCH(I28 & J28,'[LIST OF CONSULTANTS NEW.xls]Sheet1'!$B$5:$B$65536 & '[LIST OF CONSULTANTS NEW.xls]Sheet1'!$C$5:$C$65536,0),1,1),"")"

    Oh, and the other worksheet, "List of consultants new", is also open. Just to make it clear in case you guys think that it's because the worksheet is closed.

    I need help in writing in VBA Worksheetfunction a code that will give the same result as an array formula. You see I found out that why my worksheet is so slow is because of all the formulas in it. So I decided to run the workbook with manual calculation(just learned recently), and just do all calculations in VBA, which hopefully will make it faster.

    So what I need is something that maybe I'll add to the formula for Worksheetfunction so that it'll run like an array formula when activated. i.e. {=INDEX(C2:C10, MATCH(E2&E3, A2:A10 & B2:B10, 0))}

    Re: Timespan As Criteria For Getting Values

    Sorry... I was thinking it's easier to understand if I just give a simple example of my problem. I'm thinking I'll just get the basic solution to my problem, then just modify or make the formula more complex to fit my worksheet. Also, though I don't know about most posters here, I thought that by making the first post as simple as possible, it'll help other searchers to quickly understand what my threads are about. There are time when I go to a thread the first post is so long explaining everything, that by the time I'm finished I had already forgotten what was main problem,but that's just me.:smile:

    Anyway Sicarii's example gave me an idea on how to solve the problem. It's already past midnight here and my head is aching from lack of sleep already, so I'll just try that tomorrow.

    Re: Timespan As Criteria For Getting Values


    um...that formula you gave only scans "A4", it doesn't scan the whole column. It only shows the answer if "A4" meets the criteria, and it will only show the value in A4. What I want is to for "C1" to display the date in the list that matches the criteria.

    Re: Timespan As Criteria For Getting Values


    My first question there was actually kinda like asking a basic example. What I'm actually doing is a summary report based on time periods. I'm gonna use it in a payroll system. I'll try to explain as best as I could without a worksheet, since my worksheets at work.

    What my worksheet does is get all necessary information for payroll on consultants based on the time they requested payment. So if their "date of request" is under the "period range"(Stardate-endate), the worksheet will list all payroll information about that particular consultant's request.
    I was thinking that as long as I can get the "date of request", I can use it as a lookup criteria to get the other informations.

    for some reason, my excel autocorrects the formula you gave, giving me
    =and(A4>A$1:$B4<$B$2). My dates are in column A the startdate in "B1", enddate in "B2" and the formula in "C1".

    First of all, sorry about the title. I'm not good at forming questions since I usually just tell my situation to people.
    So I'll explain my situation. Let's say that i have a list of dates on column A. Then I enter a "Start date" in "B1" and an "End date" in "B2" What I want is to find all dates that falls between "start date" and "end date".
    Any help would be appreciated, or you could show me links to threads with similar subject. I can't do a good search since, as you can see from my title, I don't know the correct keywords for this type of situation.

    Re: Add New Worksheet With Event Code

    hehe... Didn't know there was a sheetchange event in the this workbook code. Thanks.
    Also I didn't used template because for some reason, when I create a template, It doesn't carry the settings when I insert a new worksheet.

    Re: Macro To Copy Macro To A New Worksheet?

    Thanks for the quick reply.
    The particular link you gave:, only shows how to copy modules, as far as I can see, and I need to copy Worksheet_change event. I'm gonna look further to the site you've given, but if that link is where I'm supposed to get it, I don't think it'll work since the code needs me to identify the module and Worksheet events don't have a name(I think).

    Is it possible to copy a "Worksheet_change event" macro to a new worksheet by macro? Like when I insert a new worksheet, a certain macro, for example "run macro on data entry", to be already written in its worksheet_change event.

    Re: Formula Reference In Object Without Code

    Sorry for the long delay in reply. I didn't notice that last post.
    For the answer, I didn't really needed the object since the only object I'll actually use is a combobox. So it's not really "reference object without code", since in the end I didn't use any object, I used a drop-down list which replaced my combobox. Sorry for the misunderstanding.

    Re: Split Text After Specified Character

    Yup, so its like taking the center word, or something like that. My example is a name: "Yambao" is the last name, "Mikhail" is the first name, and "P." is the middle initial. I pretty much know how to get the last name and middle initial, It just getting the first name I'm having trouble with.

    Re: Lookup With 2 Criteria &amp; Duplicates Exist


    shg's suggested formula needs to be confirmed with CTRL+SHIFT+ENTER.

    Is that what he meant by dynamic named range? I thought it has something to do with the "offset" formula. Well I know that CTRL+SHIFT+ENTER thing, just didn't know that it was called dynamic name range, I feel stupid.:smile: