answerstu

excel - Method Range of Object Global Failed when assigning named range

I've searched for about an hour but found nothing that solves my problem. The following code Dim q3ZFj1 As RangeDim q4ZFj1 As RangeDim q3ZFj2 As RangeDim q4ZFj2 As RangeDim q3Uebj1 As RangeDim q4Uebj1 As RangeDim q3Uebj2 As RangeDim q4Uebj2 As RangeDim cRow As LongDim cCol As LongDim startYear As Integer Dim nextYear As Integer Dim q3j1Name As VariantDim q4j1Name As StringDim q3j2Name As StringDim q4j2Name As StringDim c As VariantFor i = 3 + 1 To Sheets.CountSet q3ZFj1 = Worksheets(i).Range("C501:I512")Set q4ZFj1 = Worksheets(i).R...Read more

Excel Slicer Manipulation with VBA

I am learning how create and manipulate Excel slicers with VBA. I have been following some examples by Paul Te Braack on his "Business Intelligence Website: https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/ When I try to cycle through the items of a slicer cache and print the values of "caption", "value" and "name" in the immediate window, it returns an error of "Run-time error '1004': Application-defined or object-defined error for the line: " This is the code: "Set SL = sC.SlicerCacheLevels(1)"Sub BTest()Dim sC ...Read more

excel - Would like to use VBA to list Built-in functions

I would like to, programmatically, using VBA, generate a list (text, table, does not matter) of VBA functions available in an Access file. I am NOT looking to generate a list of user-written functions . . . I am looking to generate a list of built-in functions. I would be looking to generate a list, something like this:left,instr,date,mid,asc,reverse,month,cstr, etc., etc., etc.I you look in the VBA Object Browser, there are whole collections of VBA commands under the class names of Conversion, DateTime, FileSystem, Financial, Information, Inte...Read more

Excel 2016 - How to fill one cell with data from multiply checkboxes?

For starters, im not very great or smart when it comes to Excel. So forgive me my question, since it might be really easy, but i just cant get it to work. I have look though google and cant seem to find a solution for this, or anything that can help me in the right direction. I have a list of checkboxes in excel, from D4 to D27.I have every value of the checkboxes on E4 to E27 (TRUE or FALSE).Then i have a cell that should get the data from the checkboxes, when they are TRUE. This is B27. B27 have a =IF(E4=TRUE; "YaY"; "Nope") script at the m...Read more

excel - (VBA) Function that compute a formula (Net Promoter Score) from a list of scores

I am currently trying to create a function that compute the Net Promotor Score from a list of score ranked between 0 and 10. You can compute the NPS with this formula (the formula I am trying to code) : the pourcentage of scores above 8 (so 8, 9, 10) - the pourcentage of scores below 6 (so 6, 5, 4, 3, 2, 1 and 0)So I created this code below (with Rng as the range of cells I will choose when writing my formula) which does not seem to work. I have defined every values and still, the #VALUE error appears. Could someone please explain me where my m...Read more

excel - ListObject with Name defined by a variable or an Array

I am trying to create a series of NEW ListObjects with their Name defined by a variable or an array value.TblNameArray = Array("Table1", "Table2", "Table3", "Table4",)ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(R1, C1), Cells(R2, C2)), , xlYes).Name = TblNameArray(w)I get two new Tables with the correct names and then it stops with no error.I would prefer using a Variable, but i have not figured out how to use a variable in that location.Any help would be greatly appreciated!...Read more

Excel VBA - Storing table column into a range variable

I am currently experimenting with excel VBAs ListObjects which is the object type of an excel table. I would like to store a table column range into a variable. Here is what I can do:'store a group of cells into a range variabledim rng as Rangeset rng = activesheet.Range("A1:A10")'select a table columndim table as ListObjectset table = activesheet.listobjects("Table1")table.ListColumns(1).Range.SelectWhile both of the above work, I don't understand why the following does't work: (EDIT: it works)dim rng_column as Rangeset rng_column = table.List...Read more

excel - Loop for a string with inbuilt macro with multiple validations

I am fresh to VBA. Therefore have difficulties of getting some essential things out of code.I have a macros written for two strings. The first string is a string with values I am validating, and the second string, following the first one, is full with validation messages.What I need to do is built a loop, which would reiterate written macros to next strings if they are filled with data. Picture attached. E.G. now I have strings populated = A7:G7 and A8:G8, I need to populate next strings A9:G9 and A10:G10Code sample: 'Field 2 (E7) should not ex...Read more

angular - Render an excel sheet on Angular2 using ag grid

I have to render an excel sheet in ag grid on an angulart2 project. The data will be displayed in form of excel sheet where Columns will be maximum used columns in excel A, B, C...etc. and rows will be maximum used rows in excel 1,2,3..etc. Each cell in the sheet will have its own unique style and data. How can I render such grid in ag grid where I will set each cells styling separately without any performance lag?...Read more

Excel Dynamic Grouping/Hierarchy Tool

Let's say I have a dataset that looks like this:Raw Data (for Excel):HouseholdID HouseholdMemberID Name AnnualIncome1 1.1 Man $50,000 1 1.2 Woman $50,000 1 1.3 Boy $2,000 1 1.4 Girl $50 2 2.1 Man $0 2 2.2 Woman $125,000 2 2.3 Girl $0 3 3.1 Man $45,000 3 3.2 Girl $20 4 4.1 Man $80,000 As you can see, there are 4 columns:HouseholdID - this is what I would like the ability toroll-up to HouseholdMemberID - this is a uniqueidentifier for each record in the dataset (eg. If I am inHouseholdID = 1 and I am the 4th ...Read more

excel - Import sheets change sheet name

Im importing alot of sheetnames from different files using this VBA: Sub ImportSheets() Dim sPath As String Dim sFname As String Dim wBk As Workbook Dim wSht As Variant Application.EnableEvents = False Application.ScreenUpdating = False sPath = InputBox("Enter a full path to workbooks") ChDir sPath sFname = InputBox("Enter a filename pattern") sFname = Dir(sPath & "\" & sFname & ".xl*", vbNormal) wSht = InputBox("Enter a worksheet name to copy") Do Until sFname = "" Set wBk = Workbooks.Open...Read more

excel - One RecordSet with multiple Connections & SQL Querys

I have a VBA application that calls several sheets and obtains a set of data for each of them. All the information obtained must end in a single variant matrix.I have thought of several solutions. These are:The first of them, join the recordsets to get one only. The second one would be to sequentially dump each of the RecordSets inthe single matrixBoth solutions do not seem to be the solution ...This is the code for solution number 1:Sub Test()Dim RS01 As ADODB.RecordsetDim RS02 As ADODB.RecordsetDim Query As StringDim FField As VariantDim Pair...Read more

excel - Type Mismatch (13) error when passing Recordset object as parameter using late binding

I am writing VBA UDFs to retrieve geographic data about coded locations.I have an Excel workbook with all the details in a shared location.When the user enters the UDF on their workbook and provides the requested coded parameters, the following things happen:Their workbook opens an ADO connection to the target workbook on the shared drive, and queries all the data into a recordset.That recordset is then passed to another custom VBA function that populates a VBA Dictionary with the contents of the recordset.The UDF returns one of the values in t...Read more

excel - Formatting with method TransferSpreadsheet

actually my code is perfectly working. Code is actually creating a temporary query in Access for each supplier, i have in a table. After creating the query, next step is, saving the query as a Excel - file in my submitted path. After that, my code is creating a mail with an PDF as Attachement and with the Excel file as attachement for each supplier. But is there a possibility to formatting after or into TransferSpreadsheet - method the columns widthness after saving the Excel - file? It would be also nice if first row has a bond. Here's my code...Read more