# Posts by Halvar

• ## One more IF - dont know where to put it

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?

• ## Combine two if statements

Re: Combine two if statements

Is this what you're looking for?

Code
``=IF(N8<F8,N8,IF(AND(N8>F8,D8="Yes",C8="DCCS"),N8,F8))``
• ## One more IF - dont know where to put it

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

• ## Listview error handler if onclick is null or empty

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):

• ## Error 13 Type mismatch

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.

• ## Mobile excel noob question.

Re: Mobile excel noob question.

• ## Trying to use this code to merge multiple sheets into one but need it to also update

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

• ## Mobile excel noob question.

Re: Mobile excel noob question.

Hi Dorman, is ROUNDDOWN what you're looking for? For instance, =ROUNDDOWN((15-10)/2,0) will return 2.

• ## Exporting Specific Cells to new Workbook

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.

• ## Struggling to simplify some VB code to speed it up!

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.

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!

• ## how to compare 2 excel file?

Re: how to compare 2 excel file?

another way to do that if you're running Excel 2013 is to use Compare Files feature - it's pretty awesome and will give you more infromation about the differences between the files than standard formulas http://www.k2e.com/tech-update…013-compare-files-feature

• ## Vlookup & Index/Match

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)))

• ## Counting a column based on two criteria

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.

• ## Counting a column based on two criteria

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?

• ## Passing variable from child sub to parent sub

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.

• ## Run macro in another worksheet (Advanced )

Re: Run macro in another worksheet (Advanced )

• ## sorting copying skippin rows

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.

• ## Run macro in another worksheet (Advanced )

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)

• ## Passing variable from child sub to parent sub

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.

• ## Passing variable from child sub to parent sub

Re: Passing variable from child sub to parent sub

I see, so basically turning i into a Public variable? That might work. Thanks a lot, that's a good suggestion.