Posts by Halvar

    Re: One more IF - dont know where to put it


    ok, just to confirm I'm following your logic - is this what you would expect formula in J2 on the Home tab to return:

    Code
    =SUMIFS(sx!$X$1:$X$1000,sx!$E$1:$E$1000,A2,sx!$X$1:$X$1000,"<>",sx!$H$1:$H$1000,"Home")


    and this is for K2:


    Code
    =SUMIFS(sx!$Y$1:$Y$1000,sx!$E$1:$E$1000,A2,sx!$X$1:$X$1000,"<>",sx!$H$1:$H$1000,"Home")


    Would definitely go with this, array formulas are unstable and take longer to calculate (which might not be the issue here, just something to consider).


    Are you looking to update the whole sheet of formulas for this?

    Re: One more IF - dont know where to put it


    cześć, you sure you need SUM combined with multiple IFsto achieve this? I think you need to use SUMIF and/or SUMIFS instead. SUMIFS was designed to let you sum a range based on multiple criteria, so it seems like the way to go? sorry I can't make much of what you're trying to achieve without additional information, but hope that drives you in the right direction

    Re: Listview error handler if onclick is null or empty


    Chino, can you be more specific as to is "an error message that looks like nothing happened"?


    If you just want for literally nothing to happen, add the following to your code (NOTE: it will mask all the errors in your code, meaning any error of any kind - not just when the user clicks where she's not supposed to):


    Re: Error 13 Type mismatch


    Hey there, I don't see you declare what wbI is anywhere? Maybe start with declaring that as workbook. Also, which line errors out? I think formatting got lost as you formatted the code for posting.

    Re: Trying to use this code to merge multiple sheets into one but need it to also upd


    Jbreeden, have you considered adding a button to your workbook that will run the macro? It's very easy to do, go to Insert => Shapes, select your shape (for instance, rectangle) and draw it anywhere on the sheet. You can also change the text in the rectangle to, for instance, Consolidate. Then, right-click on the shape and select Assign Macro from the list. Select "This Workbook" from the drop-down and find your macro in the list (should be "Combine"). After you do this, whenever someone clicks on the "button" the macro will run.


    Another way to do this would be to use Worksheet_Change event and place it on all sheets that are being consolidated: http://www.ozgrid.com/VBA/run-macros-change.htm

    Re: Exporting Specific Cells to new Workbook


    freyn1, can you post the code you've come up with so far? I'm seeing this is your first post on this forum, so I just wanted to let you know that people here are usually glad to improve the code written by someone else or tackle a specific problem. But, they are rarely willing to do all the macro writing for you from scratch.


    Just a suggestion, you can start by using macro recording button (http://www.contextures.com/excel-macro-record-test.html) and see where that brings you.


    Also, this article might be helpful in finding the last populated row of data on the destination sheet: http://www.thespreadsheetguru.…-or-last-column-using-vba.

    Re: Struggling to simplify some VB code to speed it up!


    Hi there, two very easy updates for you to make that should reduce macro execution time.


    Add the following at the beginning of your code:


    Code
    Application.Calculation = xlCalculationManual 'add this after you disable events with Application.EnableEvents = False


    and then at the end:


    Code
    Application.Calculation = xlCalculationAutomatic
        Application.Calculate 'Add these two lines after you enable events


    This code turns off automatic calculations in the workbook. Simply put, every time you change a value in your workbook that's being referenced by another cell, Excel recalculates all it's formulas and values. So, turning calcs off will let Excel run the code, and then you would turn them back on and force the workbook to recalculate. This might also explain why pressing Esc key makes the macro run faster - you probably stop recalculation this way, although it's hard to tell without seeing the workbook and how you approach this.


    Hope this helps!

    Re: Vlookup &amp; Index/Match


    Is this what you're looking for?


    =IF(INDEX('Sheet with account#'!$C$2:$C$8,MATCH(B2,'Sheet with account#'!$A$2:$A$8,0))<>0,INDEX('Sheet with account#'!$C$2:$C$8,MATCH('Sheet that needs acc#'!B2,'Sheet with account#'!$A$2:$A$8,0)),INDEX('Sheet with account#'!$C$2:$C$8,MATCH(G2,'Sheet with account#'!$G$2:$G$8,0)))

    Re: Counting a column based on two criteria


    Not going to lie to you man - still struggle a bit with what you try to accomplish here..
    Try the following:
    In cells K1:V1 put numbers, 1 through 12 (1,2,3,4,etc.)
    Then, in K2 put the following formula: =IF(AND($H2<TODAY(),MONTH($H2)=K$1),"Yes","")
    Copy that formula and paste it in cells K2:V6 (it will adjust automatically).
    The formula will return "Yes" if (1) the date of the exam was supposed to happen before today's date and (2) if it's supposed to happen in a given month (those numbers in K1:V1 correspond to months, so 1 = January, 2 = February and so on).


    Hope this helps.

    Re: Counting a column based on two criteria


    So seems like the formula for your first criteria should be =COUNTIF($H$2:$H$6,"<"&TODAY()). As to the second part, can you be more specific? What are you trying to get at here? Count the number of rows where the date of the month is less than current month?

    Re: Passing variable from child sub to parent sub


    rory, as I've explained, the main purpose of my routine is not to pass the variable, that is just a "subproduct." After people have mentioned the idea I've realized this can be done, but I think it's confusing and not a good coding practice, at least not in my book. When I look at the code, I expect Functions to calculate variable's value and pass it back; when I think of Subs, I look for actions that Excel is asked to perform. Call it a personal preference, but since there's another easy solution I'd rather go with public variable.

    Re: sorting copying skippin rows


    so, it's obviously somewhat hard to give you specific advice without knows all the components, but just some random things that came to mind as I was reading through your post..


    1. you would obviously need VBA to make this all work;
    2. when setting up formulas on sheet2 - add an empty row above your sum and other calculation formulas, and hide it. Formulas would need to include that empty hidden row for a reason described further. When pasting new data on sheet2 with VBA, make sure you insert that data as a bunch of whole new rows above the empty row. This will cause all formulas in the totals row to adjust automatically (to expand or shrink) the range they're using in the calculations.
    3. the code updating everything on the worksheet can be triggered using worksheet_activate event, and make sure you disable calculations and events in Excel before running your code and then turn them back on after the code is done running. This is crucial so that activating a tab doesn't become annoyingly slow every time it's updated. Another approach would be to use worksheet_change (so, trigger the update based on changes to a specific shee - not necessarily sheet2; you can run update on sheet2 every time the source data changes, for instance).


    Hope this helps some, if you've got more specific questions fire them off.

    Re: Run macro in another worksheet (Advanced )


    Cool, so what I think you would need is a loop (basically, this is a way of telling Excel "Step through all the information I have in the specified range and do something with it"): http://www.excel-easy.com/vba/loop.html


    Also, look into ThisWorkbook and ActiveWorkbook. ThisWorkbook is an easy way to reference the workbook that contains the VBA, and ActiveWorkbook might be used to refer to the workbook you open with VBA. Some more information on this here: http://www.techrepublic.com/bl…oks-and-sheets-using-vba/ (you would probably be using this article A LOT)

    Re: Passing variable from child sub to parent sub


    PCI, I've mentioned in one of my posts that I'm importing data and doing other things in Child, so although I think it might be possible to do all that if I convert Child to formula passing the variable is not the main purpose of that routine. The variable is there just let the subs "talk" and exchange information about successful/unsuccessful macro run. But I think Public variable is a good solution here, I will go with that one.