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

    Sort by multiple columns

    Hi i have the below code to sort by multiple columns but for some reason it is not working. I do not get an error message but the code doesn't work. What am i missing? Thanks for your help With ActiveSheet.Sort .SortFields.Clear .SortFields.Add Key:=Range("D9:D700" _...
  2. Y

    Extract text

    Hi, i have the following fixed path and would like to extract the numbers from the text string. For instance, for below, i would like to extract: 97357 and 2203 without using Text to Columns but formula. Totals for 97357 Westhills Village Health Care Totals for 2203 GL - Prairie Hills Thank...
  3. Y

    Date Filter

    Hi, From a dropdown user selects the following date format for months (09-2016, 10-2016, 11-2016, etc.) and for years they select (2016, 2017, etc.) I have a macro that filters for the specified date but I believe there is a more efficient way of doing this. The way i am doing this now is...
  4. Y

    User input populate field

    Hello, I have a macro that a message box opens and the user selects "yes", or "no". If the entry is "no", i want it to populate the next empty cell in a column. How do i accomplish that? For example in the image below, if a user selects "no", then i would like the macro check the adjacent...
  5. Y

    Nested if statements

    I am trying to write a nested if statement where a user select a timeframe and then the workbook is filtered based on the timeframe and copied to another workbook. Everything works fine except for some reason if the selection is "All Months, 2016 or 2017", then it skips the section where it...
  6. Y

    Text Extractions

    How would i extract the regions from this path string without using text to column feature. I am needing the formula to extract as: Region 1, Region 2, Region 3 or Region 4 S:\SPandA\Aegis\Clinical\DOR CJR Logs\Reports\Region 3\4063 - DOR CJR Tracking Log.xlsm S:\SPandA\Aegis\Clinical\DOR...
  7. Y

    Offset and Countif

    Hi all, I have the below solutions i need help with. i have a table structure setup similar to below. I need to know how to count the number of occurences in each stage by the respective person. I know it requires a combination of offest and countif or counta and i am not that savy to do that...
  8. Y

    Hlookup with Sumif

    Hi all, please see below i am trying to sumif an hlookup. Column b is the formula and column a is the lookup and rows d1:l10 is the data table any help would be appreciated.
  9. Y

    Formula to find trailing 4 qtrs

    I need a formula that will provide me the trailing 4 quarters based on current quarter. So if we are in Q3-2016, then i need formula that will spit out Q4-2016, Q1-2017, Q2-2017 and Q3-2017. Thanks for your help.
  10. Y

    Automate word file via excel

    Hi all, I have created a macro that is supposed to open a word file and save the word document with a different name in a different location. The names are based on data in range. It seems like everything is working until I come to this code: ActiveDocument.SaveAs folderpath & "\" &...
  11. Y

    Copy only non blank filtered cells

    Hi I am consolidating various workbooks into one master workbook and some workbooks that I am consolidating have no data in the copy range. How can I modify the below code to skip or copy non blank cells. Thanks for your help. Set sh = Sheets("Hospitalized Patient") 'activate hospital...
  12. Y

    Text Extraction between hypens

    Hi, Without using text to column feature, how can I extract the text "Alabama" from the below string. I started with the below formula, but it is not working. Any help would be much appreciated. =SUBSTITUTE(MID(SUBSTITUTE("-" & J11&REPT("-",6),",",REPT("-",255)),2*255,255),"-","") 2016-03 -...
  13. Y

    Delete subfolder and files

    Hi I found a macro where I can delete all files located in subfolders but for some reason this macro is not working. Attached is the image of the subfolders Sub Clear_All_Files_And_SubFolders_In_Folder() 'Delete all files and subfolders 'Be sure that no file is open in the folder Dim FSO As...
  14. Y

    List box not working

    Hi, Need help with listbox populating. When I click on the up and down button, it gives me an error message. Please see below. The listbox that is causing the error is on the worksheet titled "hospital revenue view" The vba code are on the same sheet in the vba module and it is password...
  15. Y

    vlook in vba in external workbook

    hi all, I have the below code that I need help modifying. I am trying to do a lookup in an external workbook. Table 1 is the table where the lookup ID for the hospital is located. Table 2 is the table where the lookup value is located. I need to know how to lookup an external workbook. I have...
  16. Y

    Running macros by assigning them to values in drop down list

    Hi all, I need help in calling a macro from the selected dropdown. The name of the macro to call is State_Selection. here is the code based on drop down list Private Sub Worksheet_Change(ByVal Target As Range) Select Case Range("State_Selection") Case "AL" Call State_Selection...
  17. Y

    skip blank autofiltered table

    Hi, I am having an issue with my code. I have a code that filters and copy to a different workbook. If the filtered table has no data, i do not want to copy and move to the next parameter in the range that needs to be filtered. Here is my code below and I am getting this error message...
  18. Y

    If then Statement and numberformat

    Hi all, I have a table that changes based on the text in the dropdown. The name of the dropdown is Trend Drop down and the name of the table is Yearly Trend Tbl. Two of the metrics in the table should be formatted as percent while the remaining selection should be formatted as number with comma...
  19. Y

    listbox resizing

    to all, how are you? I created a dashboard based on selection in multi-level list box but the list box has 2 major issues: 1. it automatically resizes when the file opens so my up and down selection arrow is not visible 2. the list box doesn't automatically populate initial when it is open. I...
  20. Y

    label first and last data point on chart

    can anyone direct me to a macro that only displays first and last data point on a chart? I appreciate it
  21. Y

    macro won't work on protected workbook

    does anyone know how to make a macro work on a protected workbook. see the attached file as an example. It is protected. On sheet "GLC Trend View" the listbox is populated via macro. when the workbook is protected, it is saying my security level is too high. I have enabled all macros so this is...
  22. Y

    Listbox not populating when excel opens

    To all, I need help. I created a listbox on sheet titled "GLC Daily Trend View". For the life of me, when you open excel, the listbox won't populate. It will only populate when you click the orange down button. When you click the orange up button an error message pops up, see below. The VBA...
  23. Y

    Dashboard calculation and speed

    To all, I created a dashboard and the calculations I have are slowing the dashboard significantly. I have made some modifications to increase speed but it is still taking too long for the calculation when I change a parameter or even change views. I need help. What can I do from a maintenance...
  24. Y

    VBA Listbox and dashboard speed

    To all, I created a dashboard that utilizes a drill down listbox but the macro is causing the speed of the dashboard to go extremely slow. Can anyone help with suggestions on how to optimize the dashboard for quicker calculation? I tried using ApplicationScreenUpdating and it doesn't work...
  25. Y

    sumproduct zero value

    hello all. can someone tell me why the sumproduct is showing zero value? For the life of me, can't figure it out. It is on the sheet called calculation. Cells highlighted yellow. I appreciate it. Below is the link to the file...
Back
Top