• 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

    Hui...

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

Search results

  1. Hui

    Distribution of Values

    You do realise that this can't be solved using a linear distribution? Firstly there are 35 records with a value less than 123.9, that is records whose value is less than a value to get 150 points That uses up 5,250 points leaving 10,000-5,250=4,750 to be distributed to the remaining 13 values...
  2. Hui

    INDIRECT in dynamic arrays

    You may want to try =Sum(INDIRECT(H2#&"!$A$1") ) Ctrl+Shift+Enter or =Sumproduct(INDIRECT(H2#&"!$A$1") ) Also supplying a sample file is the fastest way to get an accurate solution
  3. Hui

    Working with pie charts

    Select your chart then select the Chart design Tab and try the various options Also select the chart and the press Ctrl+1 Explore the various options
  4. Hui

    How to draw an approximation graph in excel

    I assume that A & B are 2 sets of Y values What is the X Axis values for the data?
  5. Hui

    Concatenate Cells According a condition

    Have a look at teh following code Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Intersect(Range("B3:B35000"), Target) Is Nothing Then Exit Sub Dim break As Boolean break = False Dim myString As String myString = "" Dim i As Integer i = 1 Do If...
  6. Hui

    3D Map Hourly Timeline don't come correctly

    Add a field to your data called Date2 or anything else Make that field =A2+B2 Then in the 3D map Refresh data Then drag the new field name from the Field Picker into the Time dropdown Enjoy Time without a date assumes that the year is 0
  7. Hui

    A Difficult But Probably Easy Problem

    Have a read about the Evaluate function at https://chandoo.org/wp/lost-excel-functions/
  8. Hui

    cycle though formulas to show best result?

    I am traveling for next few days Can’t look at it til mid next week Sorry
  9. Hui

    Help Required for Charting

    Why not use a Water Fall Chart By doing that you can have an opening balance, a number of categories for Purchases and Sales and a Closing balance
  10. Hui

    Application.WorksheetFunction.VLookup code wrong

    CStr() is Convert String so CStr(lRow1) converts the number lRow1 froma Number to a Text value for us in the remainder of the formula In VBA click on CStr and press F1 for an explanation of the function
  11. Hui

    Application.WorksheetFunction.VLookup code wrong

    You are trying to execute the VLookup function in VBA by using Application.Worksheetfunction What you need to do is save the Formula as a text string representing the formula into the cells Change the line to read as below Range("C2:C" & lRow).Formula = "=VLookup($B2, $K$2:$L$" + CStr(lRow1)...
  12. Hui

    VBA to write a formula in column if cell not empty

    I have changed the Worksheet event that is used to trigger the code and added a few tweaks Replace the existing code with the code below: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub Application.EnableEvents = False On...
  13. Hui

    cycle though formulas to show best result?

    Thanx Harry Can you please explain what A1:A4 do and what range of values for each cell are allowable?
  14. Hui

    VBA to write a formula in column if cell not empty

    That code has to be in the Marciela Worksheet Object You also have to save the file a s a *.xlsm or *.xlsb file type
  15. Hui

    cycle though formulas to show best result?

    Harry There are a few ways to tackle this style problem Can you post a sample file as an example of what you want?
  16. Hui

    VBA to write a formula in column if cell not empty

    Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(ActiveCell, Range("A:A")) Is Nothing Then Exit Sub If Target.Value <> "" Or Target.Value <> " " Then Target.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" End Sub
  17. Hui

    VBA to write a formula in column if cell not empty

    Aggie81 Try the following: Sub x() Dim LR As Integer LR = Range("A" & Rows.Count).End(xlUp).Row Dim c As Range For Each c In Range(Cells(5, 1), Cells(6, 1)) If c.Value <> "" Then c.Offset(0, 2).Formula = "=XLOOKUP(A" + CStr(c.Row) + ",Items[ITEMNO],Items[DESC],"""",0)" Next c End Sub
  18. Hui

    VBA Code Gone!

    Once again hard without seeing it Remember that Excel files are simply fancy Zip files Some data like straight data and tables stores really well Other data like Pictures or Logo's don't store well Also avoid Pivot tables where you add data to a Data Model
  19. Hui

    VBA Code Gone!

    Do you have a backup ? Can you share the file here ?
  20. Hui

    Required to run Macro only if d13 has text purchase selected

    I have given you enough assistance in the above to help you Please give it a go yourself first
  21. Hui

    Required to run Macro only if d13 has text purchase selected

    It is not really clear what you are after but you have a few options Sub Purchase() If Sheets("PURCHASE").Range("D3").Text <> "Some text" Then Exit Sub 'Put the rest of the code here End Sub or Sub Purchase() If Sheets("PURCHASE").Range("D3").Text = "Some text" Then...
  22. Hui

    VBA Code Gone!

    Have a read of this: https://superuser.com/questions/1401664/excel-vba-macros-have-disappeared
  23. Hui

    VBA Code Gone!

    Paul Did you start from a *.xlsx or *.csv etc file and then add your code? If you didn't change file types to a *.xlsm that is why your code is gone
  24. Hui

    Two Old School Ways To Export Cells To A Fixed Length Text File

    I'll do it over the weekend and then send you a link to review before publishing
  25. Hui

    Unique Data Value Count in Excel macro needed

    something like: =SUM(1/COUNTIF(OFFSET(A2,0,0,COUNTA(A2:A1048576),1),OFFSET(A2,0,0,COUNTA(A2:A1048576),1))) Ctrl+Shift+Enter
Top