In Excel, I'm writing a custom function in VBA that needs to take a criteria string and criteria range like the built-in SUMIF function. Does Excel expose the functionality to test a criteria string anywhere in its API or do I have to write it myself? In case it's relevant, I'm writing a "CountUniquesIf" formula, that counts the unique ...
So I have an excel workbook that has a global mapping scheme in it. So I have a shape for each and every country in excel. Depending on the region select, relative to the data/query, it will shade regions/countries in various ways. So I know how to manipulate each shape in terms of colors, gradient shading, etc.... What I don't know ho...
Hi I have written a macro that runs at 15:30pm every workday when a workbook is first opened. When the workbook is closed it tries to open itself the next time the macro is scheduled to run. I have tried to turn the scheduler to false and am getting an error. Code below. Has anyone any ideas why this isn't working? Thanks Private Su...
Hi I have a spreadsheet with the following values: A B C D E Active Clients Cost Per Client Total Month Value 5 £100 =Sum(A3*B3) Nov Dec ...
So I have this global mapping scheme, and each country on it are individual shapes. I learned how to manipulate colors/fill based on certain criteria. So the way I do this, or the way I know how is one shape/object at a time. For example USA is "C_USA", Canada is "C_CAN", etc. So is there a way I can define countries into groups?? ie. ...
I have a 13 row range with the first row a header row and the next 12 rows filled in by a QueryTable. The query will return at most 12 rows, but possibly less. Below this range I have a totals row. I want to hide any rows that do not have data and I'm using AutoFilter to do this. When I run the code it creates the AutoFilter but doesn'...
Heres mine problem If cell H of worksheet A = cell E of worksheet B (contain words) and If cell J of worksheet A = Cell H of worksheet B (contain numbers) and If cell K of worksheet A = cell I of worksheet B (contain numbers) then copy cell O of worksheet A to cell L of worksheet B (contain numbers) (put in ot...
In my workbook, I copy the current sheet to keep as a record of a sale. Eventually, the workbook fills up with sales and at some point throws an error when I try to copy another sheet. After saving, then completely exiting Excel, then reloading the file, I can continue without problems. I'm guessing it's a memory issue, but I'm not qu...
Hi, I am using the following function to create a TableStyle: Public Function CreateTableStyle() ActiveWorkbook.Unprotect Dim objTS As TableStyle On Error Resume Next Set objTS = ActiveWorkbook.TableStyles("MyTableStyle") On Error GoTo err_CreateTableStyle If Not objTS Is Nothing Then Exit Function End If Set objTS = ActiveWorkboo...
I'm using Application run to call several macros in order like this. Sub Run_All_Macros() Application.Run ("Macro_1") Application.Run ("Macro_1") End Sub When I start Run_All_Macros, all the macros run in parallel. Is there a way to wait for the first macro to complete before the second is started? ...
I have created an Excel Spreadsheet which helps with data analysis from an Oracle database. The user enters then clicks the "Refresh Query" button which generates a query for Oracle to execute. The query takes a minute or so to complete. Although the VBA code does not hang on ".Refresh", all Excel windows remain frozen until the que...
The problem: I use VBA to populate MS Excel 2000 cells with text. The column has a fixed width (should not be changed due to layout) and the wordwrap property is set to true so the text wraps over multiple lines if wider than the column. Unfortunately, the row-height do not always get updated accordingly. I need a way to predict if the t...
I need to make an Excel add-in active/run at workbook startup....need code The add-in with parameter is: "OPCS7200ExcelAddin.XLA!StartOPC" Have been given this code: Private Sub Workbook_Open() Call Excel.Application.Run("OPCS7200ExcelAddin.XLA!StartOPC") End Sub does not work help?? ...
I'm trying to make a macro to sum a variable length column. I've tried a few different things but they haven't worked. What I want to do is: If the active cell is cell B17, I'd like B17 to equal the sum of B1 to B16. Similarly if the active cell is cell D22, I'd like D22 to equal the sum of D1 to D21. So basically it is summing all the...
Dear all, I would like to get a pair of coordinates (longitude, latitude) from a postal address. I am coding VBA in an Excel workbook. I can assume that Google Earth is installed and registered as COM server. Thus I have been looking for a way to use the Google Earth COM API to achieve this, however I have not managed to find anything....
This except of VBA code summ = "СУММ(AQ" + Format(first) + ":AX" + Format(last - 1) + ")" cell = "AQ" + Format(last) + ":AX" + Format(last) r.range(cell).Formula = "=" + summ should insert a formula, e.g. =СУММ(DW6:EI18) into DW19. (СУММ is a Russian localized name for SUM) What happens is that correct formula above appears at its p...
I'd like the X and Y axes of my Excel charts to have the same scale on the screen, because I'm plotting geographical data. A 1km by 1km square should look like a square, not like a rectangle, i.e. no squishing of the map in one or the other direction. In Matlab, the command that would do this is axis equal. How do I do this using VBA? (...
How to edit my excel dropdown list ? i went to Data>validation>Settings> In that i found the values as =Attribute_Brands What i have to do for editing this? ...
I was trying to chart some RTD data and accidentally ended up with hundreds of charts on the same worksheet. Now I'm trying to undo my error and remove all of the charts but I'm not having much luck with it. I recorded a macro where I deleted one of the charts manually and then tried editing the code to loop through all of the charts bu...
I have the following declaration in Excel VBA Public Const cdbArea = 1: Public Const cdbDist = 2: Public Const cdbChange1 = 4: Public Const cdbChange2 = 5: Public Const cdbTR = 5: Public Const crbArea = 1: Public Const crbDist = 2: Public Const crbTerr = 3: Public Const crbChange1 = 4: Public Const crbTR = 5: Public Const cdbWeek1 = 4 ...