Posts by rory

    No, they don't.

    I just noticed you said that I34 contains a formula; a Worksheet_Change event will not be triggered by a formula recalculating. You would either need to monitor the input cells for the formula, or use a Worksheet_Calculate event instead, which might look like this:

    Private Sub Worksheet_Calculate()
       ActiveSheet.Shapes("Rounded Rectangle 4").Visible = UCase(Range("I34").Value) = "PASS"
    End Sub

    though that would be triggered whenever your sheet calculates, not just when the formula changes. That may or may not be an issue.

    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:


    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:

    dim someArray()

    then resize:

    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:


    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:

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