Search results

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

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

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

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

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

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

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

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

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

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

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) =...

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

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

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

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

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

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

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")...

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

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

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

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

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

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

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