1. I

    Weird behavior SMALL

    Hi, I have a list of numbers and want to sort it while checking for criteria. My thought was to use =SMALL(IF($A$2:$A$11>$C$1,$A$2:$A$11,99),COUNTA($B$1:B1)+1) in cell B2 and extend it down. Without the IF function it works fine, but with it it acts real strange. It show a list that is in...
  2. R


    XERROR allows for conveniently generating most of the Excel errors as output to functions With XERROR, it is very easy to generate all but four of the Excel errors as output to functions. Error types 0, 1, 2, 3, 4, 5, 6, 7, 8, 13, and 14 (i.e. #EXTERNAL!, #NULL!, #DIV/0!, #VALUE!, #REF...
  3. M

    How to display these combinations

    <colgroup><col span="2"><col span="2"></colgroup><tbody> 0 0 0 0 1 1 1 1 2 2 2 2 3 3 3 3 4 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 8 8 8 8 9 9 9 9 </tbody> Code: Function ListPermut(num As Integer) 'Permutations without repetition Dim c As Long, r As Long, p As Long Dim rng() As...
  4. M

    Remove empty rows and rearrange num column

    Hello,</SPAN></SPAN> I need to remove empty rows from column C and rearrange Num column B </SPAN></SPAN> Original Data</SPAN></SPAN>...
  5. B

    Vlookup question

    hi all, can some one explain how I achieve this please. I've tried several things without success. In the function agreement I have the following: Lookup value $K9 = 0 Table Array SFG ={1,2,3,4,5,6,7,8,9,10….. Col index num 12 = 12...
  6. B

    Deleting rows if range contains value

    Hi, I am trying to run the following code below which loops through all worksheets in the workbook apart from 2; which then looks in the range ("I4:I34") in each sheet, deleting the entire row if any of these values is 0. The code seems to only delete a few of the rows containing 0 and am...
  7. rinneii

    VBA for next loops

    EDIT: I meant VBA !!! I tried doing VBA: but I ended up putting an L instead >_> For one of my assignments I need to do as follows: Obtain winning numbers through last row in column G: Type Winner in column G for any row you choose. Write a macro to capture the six winning numbers in that...
  8. P

    Formulae Help

    I have a formulae which you can see returns Y, N or # NUM! where column A is a start date and Column B is the finish date. If no finish date you get # NUM! IF(ISNUMBER(DATEDIF(A30,B30,"d")),IF(C30<=90,"Y","N"),"# NUM!") I have another formulae which looks for the result and if Y or N then all...
  9. T

    Run Time Error in Macro

    I have this macro in two different files. It runs in the first one but when I run it in the second one I get a "Run Time Error" message asking me to debug. I moved things around in the print area and this error message came up when I ran the macro. It was working fine before I moved things...
  10. V

    Match Index array formula

    I am not getting the desired out with this array formula (Column D is phone num, Column G is count, column H is D&C, column J is amount {=IF((Sheet2!$D$5:$D$800000>=$B$3)*(Sheet2!$G$5:$G$800000<=$D$2)*(Sheet2!$H$2:$H$800000=$B$3&$D$2),Sheet2!$J$5:$J$80000)} In sheet1 it should search B3...
  11. K

    MsgBox doesn't go away

    I have a macro that looks up a number and copies the information if it's an old account. The first msgbox displays perfectly; it pops up, I hit "ok" and it continues the code and moves to the next iteration. The next message box, however, continues to pop up when I hit "ok" and I don't know why...
  12. K

    VBA CountIf Assignment to constant

    I am trying to count how many times a number (Num) on one tab appears in a specified column of another tab. The Num is a variable, it is part of a do while loop where the cell itself changes and the value in the cell changes based on user input. Therefore, I have no way of knowing what the...
  13. V


    Dear all Could you help me with this issue please? I would like to sum only the transaction that does not contain "APPLE" Ex : <colgroup><col><col span="3"><col></colgroup><tbody> Num Name Debit Credit 1 APPLE 1000 sum only Num 2 and 3 1 ABC 100 due to 1 contain "APPLE" 1 001 900...
  14. V

    Vlookup/sumproduct for two or more criteria

    Dear all Could you guy help me out with the VLOOKUP. below are the table i would like to use the VLOOKUP. <tbody> Num AC Name Num 1 11002 ABC 1 Apple How to vlookup Num and meet the criteria 42 of column AC then extract value of Name 1 11001 BCA 2 Orange 1 42001 Apple 3...
  15. M

    Sum of all values found in vlookup

    Hello, I have a pivot table and an array. The pivot table has an Invoice Num (eg. 22885) and a product cost. The pivot table data is pulled from an external source (SQLDB). The array contains an Invoice Num (eg. 22885) and any freight charges that are incurred against that number. Sometimes...
  16. K

    Need help code is not performing

    Using Excel 2000</SPAN></SPAN> Hi,</SPAN></SPAN> I want to colour C:P columns as per values are set in the column R:Z, the macro were written by MickG, I been able to modified it get it work to colour row 6 only, I am not able to set all range from R6:Z19. After I run code it get stuck and...
  17. F

    Edit Randbetween macro to not include duplicates

    Hi, Could someone please help me edit this to make sure no duplicates are created? Sub RandPT_Ver2()Dim PT As Double, Num As Double PT = InputBox("Enter The Total Unique Names", "Enter a Number") Num = InputBox("Enter The 25% ", "Enter a Number") For X = 1 To Num Range(ActiveCell...
  18. K

    Please Help with Random number generator...

    Using Excel 2000 Hi, Given example below I am using formula =ALEATORIO.ENTRE(C$3,C$5) in cell C8 copied across down to P29, column Q has sum of each rows Conditions each column C:P use lower Num & Higher Num as per row 3 & 4, for example column C to H use num 2 & 4, column I use num 3 & 5...
  19. U

    Complicated SUMIFS formula help (VBA)

    Hello all, I am creating a UDF in VBA for a workbook. The workbook has two sheets: a Budget sheet and aPurchase Order (PO) sheet. Essentially, POs are entered into the PO sheet and the SUMIFS will go thru ALL the PO $ amounts and (hopefully) deduct the respective amounts from the proper account...
  20. K

    Comparing Dates and Identifying Employees

    <tbody> Name Hire Date Separation Date Qualify Agustin 7/16/2014 Num Aldo 3/17/2014 1/21/2018 No Ibrahim 1/21/2017 1/7/2018 No Luis 8/2/2017 9/3/2017 No Issaha 9/28/2016 11/20/2016 No John 2/1/2017 9/5/2017 Yes Arton 5/17/2017 Num </tbody> Hope someone can point me in the right...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back