Posts by rory

    You should never use whole column references in SUMPRODUCT formulas. Not only is it hugely inefficient but here it also means that you are including a header row in the multiplication, and you can't multiply text.


    This will work:


    =SUMPRODUCT(Sheet3!$B$2:$B$18*(Sheet3!$A$2:$A$18=A4)*(Sheet1!B1:G1=B2))


    As an aside, it's really not a great layout to have formulas that rely on two different sheets being in the same order.

    Your current version is actually missing out some of your array when it writes to the sheet. For a single column you could use:


    You cannot name an array using the value of a textbox. Why do you think you need to?


    In order to resize it using variables, you should declare it as dynamic first:


    Code
    dim someArray()


    then resize:


    Code
    Redim someArray(clng(combobox1.value) to clng(combobox2.value))

    In that case, you could do some thing like this:


    In d2 on sheet 1 enter:

    =IF(TRIM(D4)="Core function actual",COLUMN(),"")


    and copy across to the end of your data.


    Then on sheet 2 in C5 enter:


    =IFERROR(LOOKUP(2,1/(Sheet1!$B$5:$B$20=$B5)/(Sheet1!$C$5:$C$20=C$4),INDEX(Sheet1!$5:$20,0,MINIFS(Sheet1!$2:$2,Sheet1!$2:$2,">"&MATCH($C$3,Sheet1!$3:$3,0)))),0)


    and fill across and down for the 6 levels. You can then copy and paste that across to the next 6 and replace $C$3 with $I$3 and repeat for each block of 6. Alternatively, you could enter the date in each cell in row 3 on sheet 2 (you can hide any you don't want visible using number formatting) and then just amend the $C$3 in the formula to C$3 and you can then simply fill across for all the data.

    In B7: =ROUND($B$1*B3/SUM($B$3:$B$5),2)

    B8: =ROUND($B$1*B4/SUM($B$3:$B$5),2)

    B9: =B1-SUM(B7:B8)


    the formula in B9 just ensures there are no rounding issues.

    Basically ignore them.

    That doesn't really make sense. You have to do something about them or your totals will be too high/low. The question I asked is what do you want to do about them? Given the example you posted, what should be the outputs in each cell?

    How do you want to allocate any over/under amounts? If it's pro rata across all three, then you just need to scale the percentages in each cell accordingly. So in your picture, they add up to 110% so you'd divide each percentage by 110% before multiplying by B1.

    You cannot convert a delimited string to an array simply by passing it to the Array function - you just end up with an array with one element that is the string you started with. You can use Split though:


    Code
    Worksheets(Split(SheetsToPrint, ",")).Printout


    for example, where B1 contains just:


    HOME,Accounts,Expenditure report,Supporting schedules,Payments

    It usually helps if you explain the logic of what you want rather than leaving us to guess what a non-working formula should be doing. Maybe:


    =IF(OR(P2={"B2C",""}),"B2C",IFNA(IF(VLOOKUP(P2,'GSTIN Verified'!$D$2:$H$16,5,0)="ACT","B2B","B2C"),""))

    Quick question - are you on version 2212 by any chance? I've seen quite a few reports recently of weird errors with workbooks with userforms on that build. There is apparently a fix in the works.

    If you only need this range to be processed in a macro, why not just pass the dynamic range you already have and simply process every other row in the code (Pretty much as Carim showed near the beginning of this thread). Or do you need this named range for something else?

    When you show a form, it is first loaded into memory and any code in its Initialize event is run (this is usually where the problem with a userform.show line actually is) then the form is displayed and any Activate code is run.


    As a side note, it is generally better in my experience not to run code directly from the Workbook_Open event. If you use Application.Ontime to schedule any startup code (you can use Now as the schedule time), it gives Excel time to finish all its startup processes (particularly necessary when you open the workbook directly from explorer, thereby also loading excel).