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

  1. Smallman

    Cells.SpecialCells function help

    Hi Frisbeenut It is a bit quiet in my world so I decided to try editing your original code. The advantage is it will only focus on the cells with formula. Sub ConvertFormulasToAbsolute() 'Add a reference for the cells in questions Dim rng As Range Dim i As Integer i = InputBox("Add a number...
  2. Smallman

    VBA Code for UDF JoinText

    Because everyone knows you Jindon - you are a Jedi Master, your rep precedes you wherever you choose to post!!!!!:)
  3. Smallman

    Welcome to a new Chandoo.org Ninja

    Welcome aboard Deepak. :) Your efforts contribute to a thriving community.
  4. Smallman

    Macro for vlookup dynamic range with two worksheets

    Hi If you set up your spreadsheet so the data is pulling off one cell then you should be able to get the job done the following way. Sub Redo() [G11:G18] = "=vlookup(D11, '" & [G8] & "'!$D$11:$F$18,3,0)" End Sub Then you could run it off the change in G8. Private Sub...
  5. Smallman


    Hi Dparteka This bit: Not at all. You did not supply a file and one would assume if you are starting your criteria search in Row 18 that your headings are in Row 17. If I was to apply the coding from Row 18 the first item would be left out of the criteria so move back one. I am not...
  6. Smallman


    Hi dparteka Perhaps attack all of the cells in one hit. Option Explicit Sub Testo() Range("A17", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, "<=0" Range("A18", Range("V" & Rows.Count).End(xlUp)).ClearContents [a17].AutoFilter End Sub Take care Smallman
  7. Smallman

    Delete the Zero values

    Hi Abhijeet Give the followining a try. Sub GetRid() Dim lr As Long lr = Range("A" & Rows.Count).End(xlUp).Row Range("B2:L" & lr).Replace "0", "" Range("L2:L" & lr).Formula = "=SUM(B2:K2)" Range("L1:L" & lr).AutoFilter 1, 0 Range("L2:L" & lr).EntireRow.Delete Range("L2:L"...
  8. Smallman

    Question about Chandoo example macro: Consolidate data from different excel files (VBA)

    Hi Dwee You really should start your own thread with a link to this thread. To be honest I don't really like the way this file has been set up. I was just copying a template from earlier in the thread and applying some logic. I would prefer a little more about your problem. For instance...
  9. Smallman

    Dashboard Ideas

    Hi Rico Congratulations. That is a massive improvement in a short time. I like the look and feel of the dashboard. Well done!!!! If I were going to change anything, the charts at the bottom of the page, I would make the colour scheme, blue, brown and yellow which is consistent with the 3...
  10. Smallman

    VBA to create an array from a filtered Excel Table

    Hi I don't think that what I provided is too difficult to grasp. In this instance I would use my own approach as I understand it and it avoids looping which is gold as far as I am concerned. For larger datasets and for people coming to view this thread in months, years to come the following...
  11. Smallman

    VBA to create an array from a filtered Excel Table

    Hi David How about you just take the loop right out of the mixing pot. I would steer clear of the If statement. Not sure if you have read this before. These are sage words from the late great David Hawley. VBAGoldenRules. I particularly like the use of sheet code name and avoiding loops...
  12. Smallman

    VBA for Inactive Cell Default to Empty

    Yeah that would be better. But if we were going to call coding 'Bad' every time I saw something that met that criteria I certainly would not have enough hours in the day to add some value. Generally I tend to just drop what I consider to be an improvement to the post. :) Don't always get it...
  13. Smallman

    VBA for Inactive Cell Default to Empty

    If more than 1 cell is highlighted the code exits with the enable alerts off rendering the code unusable till someone turns events back on. When posting a workbook with code inside, it is nice to see the VBA that goes with the file, especially when small like that above example. It helps...
  14. Smallman

    If False then...

    Hi Yandeez This should do it. =IF(F10=E10,"Yes",1.5) Format the cell as a % Take care Smallman
  15. Smallman

    Excel Presentation

    Just ask them to join the school as well. That way they are getting an expert teaching them and their learning will be expedited.