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

    VBA Stops after opening file

    Hi Marc L Thank you for the reply. The ugly module has been removed and your script pasted in the Sheet1 worksheet module. For some reason, the file being opened remains the active workbook despite ThisWorkbook.Activate I am not sure if this may affect things, but I am using Excel Version...
  2. S

    VBA Stops after opening file

    Hi vletm, Thank you for your reply. I have prepared a working example to demonstrate the issue. Once Workbooks.open OpenFile executes it does not return back to Chandoo Analysis.xlsm as the active workbook, the code appears to pause. If I manually make Chandoo Analysis.xlsm the code will...
  3. S

    VBA Stops after opening file

    Hi Guys, I am having an issue where the macro runs but once it opens another workbook, it just stops at Workbooks.Open OpenFile Once the second workbook has been opened, the active workbook WBName is the workbook I would like active. I initially had this in a worksheet to run when a cell...
  4. S

    Embed Google Map in to Excel

    Hi Ali, Thank you for your reply. My issue is not so much about the maps or the API, it is about getting the map to display with Excel. Previously it could be done through the ActiveX Control "Microsoft Web Browser", however this functionality appears to have been removed in Excel 2016...
  5. S

    Embed Google Map in to Excel

    Hi All, Using Excel 2016, I would like to be able to insert a Google Map image for a given address. This could be either an embedded map or just an image of the map The workbook may call for multiple embedded maps or images of maps for multiple addresses. The Microsoft Web Browser add-in does...
  6. S

    Find text on webpage and click underlying link VBA

    Hi All, I have a list on a webpage: Coy 1 Coy 2 Coy 3 and I want Excel to "click" on Coy 3 and load the corresponding page. With the underlying webpage code being: <a href="/gdfd/Do/Something/FTRED" class="name" data-automationid="dashboard">Coy1</a> <a href="/gdfd/Do/Something/hdYnd"...
  7. S

    make UDF behave like other functions

    Hi Marc L Because I had no idea it existed. In the example I used cell fill colours but I am actually using it for broken out chart properties. Would your method work in charts where the RGB colour value was taken from a cell interior.color and applied to a pivot chart in something like...
  8. S

    make UDF behave like other functions

    Hi All, Just some follow up with respect to the UDF and more importantly what I was trying to achieve with that UDF. What was I trying to do: Use the above UDF to get the RGB values (in the string format xxx, xxx, xxx) of a cell and use in other subs. This was never going to work, as I found...
  9. S

    make UDF behave like other functions

    Good afternoon Hui, Thank you for your reply and the link. I will look into it. Cheers Shaun
  10. S

    make UDF behave like other functions

    Hi Guys, I am trying to get the RGB values of a cell and I have found a function which does that here. Function Color(rng As Range, Optional formatType As Integer = 0) As Variant Application.Volatile Dim colorVal As Variant colorVal = Cells(rng.Row, rng.Column).Interior.Color Select...
  11. S

    Find and add missing months to table

    Hi All, It ain't pretty, but it gets the job done! Sub MissingDates() Dim RowNum As String Dim LstRow As String Dim LstRow1 As String Dim LstRow2 As String Dim LstRow3 As String Dim strSearch As String Dim Mth(1 To 12) As String Dim sMth As String Application.ScreenUpdating = False Mth(1) =...
  12. S

    Find and add missing months to table

    Hi All, I have a list of dates which are assigned: Financial Year; Financial Quarter; and Month. What I am trying to do is identify missing months in each of the financial years in the table and add the dummy data into the tables. In the example attached the months missing are January 2015...
  13. S

    Pivot Tables - Add Calculated Field with Grouping

    Hi Chihiro, I think I should be able to make the changes as suggested. Can I still use the grouping mechanism (as is in the file provided via pm), which yields a | delimited string (4-1000|4-1001|4-1002|etc)? Cheers, Shaun
  14. S

    Pivot Tables - Add Calculated Field with Grouping

    Hi Chihiro, Thank you for you reply and suggestions, I appreciate them. You are suggesting I use multiple tables: A table for AccountNumbers; and then A table for each of the groupings: Income COGS Expenses Etc In the file I sent to you via pm, the groupings are defined by the user via...
  15. S

    Pivot Tables - Add Calculated Field with Grouping

    Hi All, I am trying to insert a calculated field where the values to be inserted are 50% of the total of a grouped set of columns. I have found many examples of ungrouped pivot tables, but not on grouped tables. In the example attached, I would like to add the calculated field of "Budget"...
  16. S

    Calculations within pivot tables

    Hi Chihiro Thank you, I knew there would be a better way...I just couldn't find it. Thank you Shaun
  17. S

    Calculations within pivot tables

    Hi All, I am having a little trouble trying to work out whether it is possible to perform calculations on grouped items within a pivot table and subsequently generate a pivot chart? I have not been able to work out an "elegant" way to do it, just the clumsily put together example in the...
  18. S

    Filtering pivot table from array

    Hi Narayan, Thank you, your advice helped immensely! I played around with the code and came up with: 'Sets visible property to all PivotItems except the last to False, 'but stores I for property changes later. With ActiveSheet.PivotTables("PVTData").PivotFields("Account" & Chr(10) & "Number")...
  19. S

    Filtering pivot table from array

    Hi All, I am trying to work out how to generate a pivot table using only those accounts select and stored in two arrays (DataSet1Array() and DataSet2Array()). The trouble I am having is using each account number in the array to determine whether an item should be visible or not. The best I...
  20. S

    Creating a Dynamic Calendar Table

    Hi David, I am sure there is a better solution available, but the attached works. Cheers, Shaun
  21. S

    VBA SQL Query to return calculation

    Hi All, I have recorded an SQL Query which I would like to amend to include a calculation where two columns in the table are multiplied. The query: With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & fpth & ";DefaultDir=" & fdir &...
  22. S

    Excel to Word

    Hi Belle23p I forgot to mention that you will need to reference the Microsoft Word 1?.0 Object Library in the VBA editor under Tools -> References. Cheers Shaun
  23. S

    Excel to Word

    Hi Belle23p This should get you started, hope it helps. Cheers Shaun
  24. S

    VBA and Queries

    Hi Narayan Thank you for the additional links. That last link is a treasure house. Thank you. Shaun
  25. S

    VBA and Queries

    Hi Narayan Thank you for the links. If you know of anymore please let me know. Cheers
Back
Top