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

    ClearContents

    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

    ClearContents

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

    Extract data from sheet to another

    Using Mark's concept this procedure produces the same result. Sub MoveAdv() Sheet2.[F8:H40].AdvancedFilter 2, [D47:D48], [C50] End Sub It is worth considering for its simplicity over scripting Dictionaries which are beyond most Excel users. File attached to prove workings. Take care Smallman
  17. Smallman

    VBA_HELP_02/17/2015

    No I am sorry I don't agree with Merged cells. They are a nuisance IMO and should be avoided at all costs. Centre Across Selection produces the same look without the loss of fidelity. Take care Smallman
  18. Smallman

    VBA_HELP_02/17/2015

    Hi Ravindra Should do everything you wish with the dropdown alone. Take care Smallman
  19. Smallman

    Extract data from sheet to another

    Hi This fixes the issue for you. Public Sub GB() Dim oneRange As Range Dim aCell As Range Dim x, y(), i&, j&, k& Dim LastRow As Long x = Sheets(2).Range("F8").CurrentRegion.Value ReDim y(1 To UBound(x), 1 To 3): j = 1 With...
  20. Smallman

    VBA_HELP_02/17/2015

    Hi Ravindra It is a false positive. So Not and False are like saying (minus minus) or -- which is the same as True. As such the coding can be shortened and I think you will grasp this more easily; If rng <> [Y1].Value Then rng.EntireColumn.Hidden = True Try it out - does the same thing...
  21. Smallman

    Basic question on VBA autofilter "Field" code.

    Here is an example workbook with coding. Sub TestFilter() Dim iCol As Long iCol = 25 [A1].CurrentRegion.AutoFilter iCol, 1 End Sub No errors. Take care Smallman
  22. Smallman

    VBA_HELP_02/17/2015

    Hi The code is saying any cell in Row 1 which does not contain the same value as that which exists in Y1 then hide. The method is outlined in more detail here with a file to show workings. Hide Cols Hope that helps. Take care Smallman
  23. Smallman

    Dashboard Ideas

    Hi Rico In your first chart your space is too small to be showing 69 items all with labels in a single chart. It is too messy and no information can can gleaned from your chart rendering it useless. Show your chart at a higher level or take the names out so the chart shows a flavour rather...
  24. Smallman

    Chandoo also launch Microsoft access forum

    I have no doubt that if Chandoo decided to start an Access forum it would be an outrageous success. However, currently there are brilliant MS Access forums with data bases of information which are extremely impressive. I rate Utter Access and Access-Programmers but let’s be clear, there are...
  25. Smallman

    Macro for hiding rows very very slow

    To add to what Luke said if you remove the looping construct and set up a helper column (M) the following should replace your coding. Sub HideBlank() [M8:M306].AutoFilter 1, "=", , , 0 End Sub I have replicated your structure in the attached. If I didn't get the formula perfect just change...
Back
Top