Posts by rory

What is the actual formula in I34?

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.

The code must be in the worksheet code module for the sheet with the cell whose value you are monitoring. It will not work in a normal module.

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:

Sub FillRangeFromArray()
Dim m As Long
Dim X As Long
Dim Y As Long
Dim myarray() As String
m = 15
ReDim myarray(0 To m, 1 To 1)
 For X = 0 To 3 
 For Y = 0 To 3 
 myarray(X * 4 + Y, 1) = X & "-" & Y
 Next Y
 Next X
 ActiveSheet.Range("A1").Resize(m + 1).Value = myarray
 End Sub

At some point, you'll also want to look at Chip's pages where he put a wealth of functions for working with arrays - e.g. here: http://www.cpearson.com/Excel/VBAArrays.htm

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

Glad we could help, and thanks for the kind words - they have made my day!

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.

Can you add any formulas to Sheet1?

This is clearly homework of some kind, so I will just say that you are missing a function called fillarray and you should not have declared it as a variable.

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.

Quote from DANGPS

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.

From what I've heard, the latest build seems to now correct most of the weird errors that people had been getting.

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

Replace the @ with commas, then use Split with a comma as the delimiter. Then loop through every other item in the resulting array (using Step 2). It's not clear to me what output you are expecting.

You could also achieve this with a formula.

Your formula in post 8 is an array formula and works fine. The issue is with formulas that return arrays of references. (formulas that return arrays of values clearly can't be named ranges)

I know you know the rules on cross-posting. Please update your post with the required link(s) and consider this your last reminder. Thanks.