# Recent content by Hui

1. ### Which formula can i use to find date of resignation fallen in the financial year

=IF(MEDIAN(A2,B2,D2)=D2,"yes","no")
2. ### Minus symbol in SUM function

I have seen a lot of people do this =Sum(B2-A2) who have learnt the Sum formula before understanding what ranges and formulas are As described above =SUM(B2-A2) is exactly equivalent of =B2-A2 the Sum function isn't needed Peter Bartholemew's description of using named formula is also true...
3. ### PrintPreview issue

Jolivanes One of the most frustrating issue when answering posts on public forums is when people don't respond. However there are also many reasons why people don't or get delayed. It is only 7 days at this stage This is one reason why we prefer people not to post on multiple forums They get...
4. ### How to use Range as table

Try: strSQL = "Select Distinct [Product] From [Sheet1\$A1:G10] Order by [Product]" or strSQL = "Select Distinct [Product] From [myRng] Order by [Product]" where myRng is a named Range to Sheet1!A1:G10
5. ### In addition to the existing formula IF >=75 ok OR IF = "text" return value in orginal column

C2: =IFERROR(IF(A2>=75,"Ok",IF(A2="Not enough data",B2,LOOKUP(\$A2,Calc!\$H\$2:\$H\$5,Calc!\$J\$2:\$J\$5)+(SUM(100-\$A2)*(LOOKUP(\$A2,Calc!\$H\$2:\$H\$5,Calc!\$J\$2:\$J\$5))+\$A2))),"") Copy down
6. ### What other programming languages do you use?

Harry0 Excel isn't technically a programming language, although effectively it achieves the same result by laying out formulas on a worksheet/named formula that get you to a result. Excel has 3 programming languages/systems built in Excel Macro's, replaced by VBA in 1995 VBA, Used since 1993...
7. ### Unable to get match property in WorksheetFunction class Error!

You can’t divide by 0 in Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%") so use If weight = 0 then Entry_Form.Score_Achieved.Value = Format(0, "#.0%") else Entry_Form.Score_Achieved.Value = Format(score / weight, "#.0%") end if
8. ### vba macro

In your first pouint it is: Workbooks("1.xlsx").Close now it is Workbooks("1.xls").Close They are not the same filename
9. ### vba macro

The line Workbooks("1.xlsx").Close will close the file if it is open if you don't want to save changes use Workbooks("1.xlsx").Close SaveChanges:=False
10. ### How to calculate XIRR without projecting each cashflow

HSR You could use a very complex Named Formula to develop a list of values based on your criteria, but you also need a Named Formula for the Dates, that would be a mess, but probably doable. The easiest way is to setup 5 rows or columns, one for the date and one for each cash flow scenario Add...
11. ### Unable to get match property in WorksheetFunction class Error!

Did you look at the Sub Add_Scores() ? The line whole sub is new, I added it The final line puts the answer in Entry_Form.Score_Achieved.Value = CStr(score) & " / " & CStr(weight) So change that to suit your needs To get percentage use: Entry_Form.Score_Achieved.Value = CStr(100 * score /...
12. ### Unable to get match property in WorksheetFunction class Error!

Look at my changes you can work out what to change from there
13. ### Unable to get match property in WorksheetFunction class Error!

As for the second question, Yes Pls see the attached
14. ### Unable to get match property in WorksheetFunction class Error!

Change your code to use the following Private Sub Command_Search_Click() Dim iRow As Integer iRow = Sheets("Data").Range("G2:G1000").Find(text_Search).Row - 1 MsgBox iRow End Sub
15. ### Generating a set of 2000 random numbers and save the Generated set in Adjacent coloums.

Select A1:F2000 (That's only 1999 numbers) Goto the Data, What-If Analysis, Data Table menu Set the Row Input cell as a cell not being used say AA1, apply every time you press F9 it will update You also talk about 1440 minutes but your Random formula is Randbetween(1,120) If you want to...