• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by Hui

  1. Hui

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

  2. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    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. Hui

    Unable to get match property in WorksheetFunction class Error!

    Look at my changes you can work out what to change from there
  13. Hui

    Unable to get match property in WorksheetFunction class Error!

    As for the second question, Yes Pls see the attached
  14. Hui

    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. Hui

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