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

Search results

  1. Belleke

    Clear contents of a range when slicer filter is cleared.

    I have a slicer on a sheet, I would like to clear A1:B10 when Slicer filter is cleared. (nothing selected)
  2. Belleke


    I am new to excel. See question in attachement. Thank you for helping.
  3. Belleke

    quarter year in excel userform textbox

    I have Textbox1.Value = Format(Date, "dd/mm/yyyy") and Textbox2.Value = Format(DatePart("q", Textbox1.Value)) The result in textbox 2 is wrong, it uses the American date notation example 02/05/2019 gives as result 1(uses 02 as month) but it should be 2 (5th month, second quarter) How can I...
  4. Belleke

    Clear contents of certain columns.

    I have Set Rng = [ANRs] Set fnd = Rng.Find(What:=T_26.Value, LookIn:=xlValues, LookAt:=xlWhole) If Not fnd Is Nothing Then smessage = "Afrekenen, ben je zeker" + "?" If MsgBox(smessage, vbQuestion + vbYesNo, "Bevestig Afrekenen") = vbNo Then GoTo oops ws.Rows(fnd.Row).ClearContents I want to...
  5. Belleke

    Disable login when opening excel

    For some time now a friend of mine is getting a message from "Windows Security" that he needs to login to his account. He wants it to be disabled butI don't know how to disable that. Any one an idea? Thanks.
  6. Belleke

    Double in Slicer

    What is the reason that I have a double in slicer?
  7. Belleke

    Workbook doesn(t want to save

    I have this code Sub Belle() Range("A2:R50").Copy Workbooks.Add With ActiveSheet .PasteSpecial .PasteSpecial xlPasteColumnWidths End With Path = "C:\test\" Filename = ActiveSheet.Range("P7") ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsm", FileFormat:=52 ActiveWindow.Close End...
  8. Belleke

    5 highest values formula

    I have this formula in one cell =INDEX(C2:C411,MATCH(MAX(P2:P411),P2:P411,0)) What would be the formulas to display the five highest scores in five cells. The names are in column C, scores are in column P There can be identical scores. Thanks
  9. Belleke

    Color userform labels background from listbox selection.

    I am looking for a code that changes the background color of labels. If I use Select case the code will be long. I wonder if it' s possible to have another one with a shorter code. thanks
  10. Belleke

    Values instead of formulas

    I have this code Private Sub Cmd1_Click() Application.ScreenUpdating = False Set j20 = Sheets("JAP 2020") lrJ20 = j20.Cells(Rows.Count, 1).End(xlUp).Row If lrJ20 < 5 Then lrJ20 = 5 j20.Rows(5 & ":" & lrJ20).ClearContents Set R = Sheets("Resultaten") GoSub kopieer Set R =...
  11. Belleke

    Lowes scores only color once

    In a file the lowest scores are colored with CF. You can select number of lowest scores in N2. 2 lowest scores, 3 lowest scores ...... How can I change that if there are double scores cells should only colored once. for example in row 8 number 46 should only colored once. You can use VBA. Thanks...
  12. Belleke

    Shorter code?

    I have a sheet called weekplanning and 5 sheets with the (working) days. In weekplanning Data for MO is in column b TU in column c We in column D TH in column E Fr in column F These data has to go the daysheets, all starting in B4 I think that this code could be shorter, but I don't see it. Sub...
  13. Belleke

    Shortcut created.

    After opening an excel file on a network drive, a new desktop shortcut is created each time. Does anyone know the cause and a possible solution?
  14. Belleke

    Transfer from sheet 1 to sheet 2 with column swap

    Hi, I am looking for some code to transfer data from Sheet 1 to sheet 2 when value in column E is ready. But two difficulties The transfer should start at row 10 and values from column G should go to Column F See example Thanks
  15. Belleke

    go to the next emty cell in column

    Hi, I have this. Set rng = Range("C2") For i = 0 To LB_00.ListCount - 1 If LB_00.Selected(i) Then rng.Value = LB_00.List(i) Set rng = rng.Offset(1) End If Next i How can I change rng.Offset(1) to Set rng= rng.Offset next empty cell in column C. Thanks
  16. Belleke

    Cell reference instead of value

    Hi, I want to replace test in this formula with the value in List!B3 C:\Users\Belle\Documents\chandoo\test\[Data.xlsx]Sheet1'!A1 I tried a few things like indirect but it give me errors. Thanks
  17. Belleke

    Compare 2 userform listboxes

    Hi, I have a multiselect listbox (1) and a singleselect listbox.(2) I am looking for code, if I select item(s) in listbox 1(multi) the code should check if item(s) already exists in listbox 2, if so then a msgbox item already exists. To prevent a name is selected 2 times.
  18. Belleke

    Sum formula is not correct.

    See the yellow cells in the example. It only sums the numbers with decimals,other cells are seen as text. Cell properties are correct. I can't find why. Thanks in advance.
  19. Belleke

    Copy 3 column of a table on sheet 1 to another table in sheet 2

    Hi, I have this code that works except that the data has to go in the table on sheet 2, this code puts the data under the table. Private Sub Cmd_09_Click() Set DT = Sheets("Data") Set NB = Sheets("Nog te betalen") On Error Resume Next With DT ar = .Cells(1).CurrentRegion ReDim...
  20. Belleke

    Make sum of duplicate names in list.

    Hi, I would like to sum double values. I made an example with what I have and what I would like to achieve. Thanks in advance.
  21. Belleke

    Pivot table age and gender.

    Hi, I have a sheet Data with a table. In the second sheet I need A pivot table with age and gender. See example. Thanks.
  22. Belleke

    Copy to other sheet.

    Hello, I have this code Columns are switched. With Sheets("Sheet1") .Columns(6).Copy Destination:=Sheets("Sheet2").Columns(1) .Columns(1).Copy Destination:=Sheets("Sheet2").Columns(2) .Columns(7).Copy Destination:=Sheets("Sheet2").Columns(3) .Columns(8).Copy...
  23. Belleke

    Workbook open from userform selection.

    I fill a userform listbox with this code MyFolder = ThisWorkbook.Path & "\Attendance" MyFile = Dir(MyFolder & "\*.xlsm") Do While MyFile <> "" LB1.AddItem MyFile MyFile = Dir Loop LB1 is a the listbox Attendance is the folder Then I use this to open a workbook. Private Sub...
  24. Belleke

    Sort members

    Hello, I have a sort problem. I need to sort a huge list of members, I want to sort them from A to Z but the same adresses should stay together sorted on the first name. See simple example. Thanks.
  25. Belleke

    Count except Sa and Su.

    Hi see the picture I would like a formula that counts how many times 25, except saterday and sunday (Za, Zo) Thanks