FAQ Database Discussion Community


Offset function returns #VALUE! with small dataset

excel,excel-vba,excel-formula,excel-2010
I have some data in excel and want to access certain rows with the offset function, however the function returns #VALUE! for my small dataset. Using this, OFFSET(A2;0;0;COUNTA(A2:A99999);1) I get #VALUE!. When changing the formula to OFFSET(A2;0;0;100;1) I get a value. Any suggestions, what I am doing wrong? I appreciate...

excel: If I search for a MIN value in a range, and 2 values are MIN, it only displays the first

excel,excel-2010
I have a function where I look for the MIN value in a range, lets say 9, 18, 12, 9. Both 9's are the MIN, but MIN only looks at the 'first' min value. I want to be able to also give the 2nd '9' in my range as an...

Unable to assign formula to cell range in Excel

vb.net,excel,excel-formula,interop,excel-2010
Someone else's code in the project, that I am trying to fix up. listO.Range(i, j).FormulaR1C1 = FormulaMatrix(i, j) where FormulaMatrix(i, j) is always a String value. Whatever random/test value, I try with, is being assigned successfully, except when it is a formula, eg. =IF(LENGTH([@Units])>0;[@SalesAmount]-[@DiscountAmount]0) If I remove the = sign...

Optimizing Excel formulas - SUMPRODUCT vs SUMIFS/COUNTIFS

excel,excel-formula,excel-2010,formula
According to a couple of web sites, SUMIFS and COUNTIFS are faster than SUMPRODUCT (for example: http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html). I have a worksheet with an unknown number of rows (around 200 000) and I'm calculating performance reports with the numbers. I have over 6000 times almost identical SUMPRODUCT formulas with a couple...

Find range of column, given column number VBA

vba,excel-vba,excel-2010
How would I find and store a range object for an entire column, given the column number? Thanks!

Excel - Sum of values based on a drop down defining whether they are negative or positive

excel,excel-formula,excel-2010
I've tried searching for posts specifically on this, but am struggling to find what I'm looking for. I'm trying to do some fairly basic calculations based on a value in a drop down field for each row which determines whether the value is negative or positive. In the example below,...

SIMPLE Delete line from cell?

excel,excel-vba,excel-formula,excel-2010,excel-2007
Hello I'm trying to delete all the rows where in column B the members value is over 1000. I tried this step by step and tried first getting rid of all the unecessary data from B cells and leave just the line with the members. I noticed there are 5...

Excel 2010 Ribbon Calling Macros

excel-vba,excel-2010,ribbon
I have an Excel AddIn (.xlam file) and within it is a few macros and my attemt at a custom ribbon tab. The Macros work as expected but now I am trying to make a ribbon to call them to be more user friendly. I have the ribbon and a...

Selecting all data in other worksheet VBA

excel,vba,excel-vba,excel-2010
I am new to VBA and I am trying to write some code that selects gets the range of all of the cells in a different worksheet than the current one, prints it in a message box (for testing purposes), and returns to the original sheet. I intend to use...

Find #REF in Row and Delete Cell

excel,vba,excel-vba,excel-2010
I have a spreadsheet in Excel 2010 that sometimes has a reference error. I want to clear the cell in that particular row; ie. Clear any cells in Row 7 that contains a reference error. I'm having some trouble with it. Sub Check_Ref() Dim rng As Range Dim rngError As...

VBA recognizing workbook by partial name

excel,vba,excel-vba,excel-2010
Is there a way to specify a workbook for a sheet without having the full name? For example, If the workbook name is MyWorbook2015 and the 2015 may change to 2016 in the future, I need to to recognize the workbook based on MyWorkbook, and ignore the 2015. Something similar...

Delete the string after the delimiter in excel

excel,excel-formula,excel-2010,excel-2007
I have simple problem. I was analyzing some data and came up with this problem. Below is my value in a colomn: www.mysite.come/api/Customer?id=12333&name=jack www.mysite.come/api/Department?id=52365&name=COP www.mysite.come/api/Customer?id=13333&name=mathew etc I want to filter this data something like this www.mysite.come/api/Customer www.mysite.come/api/Department www.mysite.come/api/Customer Please help me with this....

compile error expected list seperator

html5,excel,vba,excel-vba,excel-2010
Trying to modify a macro I have in excel and I am running into a problem with it. I am getting the message "Compile Error: Expected: list separator" Here is the working vba code Sub GetTitles() Dim c As Range, url As String For Each c In Columns("B").Cells url =...

VBA =COUNTIFS from different file, using variable for R1C1

excel,vba,excel-vba,excel-formula,excel-2010
so I am trying to turn this formula into VBA code: =COUNTIFS('Report'!$J:$J,"=In Force") However, I have acquired the column in the 'Report' file that I will be extracting the data from. The column number it stores in variable ColNum. I need to ensure that it extracts data from the column...

If statement and addition

excel,excel-2010
I am trying to look if a cell value in second sheet is not equal to blank and then check the value of other cell is lesser than 3 and further update the value to 1 or 0. 1st condition if g2 cell value present in second sheet is not...

If statement Compile error

excel-vba,if-statement,excel-2010
I'll try to make a macro with an If statement in it. But I get the error message Sub or Function not defined The following code is used. Sub whatif() If Range("memory") = "data" Then Worksheet("Side").Range("B1") = "yes" End If End Sub I figured out the problem is line Worksheet("Side").Range("B1")...

How do you lookup data in Excel matching multiple columns (i.e. date & product, date & region)?

excel,excel-vba,excel-formula,excel-2010,excel-2007
http://imgur.com/BYejKRb (please use the sceenshot linked above for reference - this is my first post so I can't post images) As you can see in the picture above, I want to take Table 1 and, using formulas that can auto-populate, turn it into Table 2. I tried using vlookup and...

Getting hours between 2 dates

excel,excel-2010
A1 = 29/05/2015 14:07 B1 = 01/06/2015 16:02 C1 = =(B1-A1)*24 Why am I only getting 22:00 in C1? I was expecting it to count all the hours on the weekend too......

Conditional Formatting for every row

excel,excel-2010,conditional-formatting
I am trying to highlight the max value in each row of data to determine what year it falls in. Is there a simple way to apply it to the whole spreadsheet? The only way I can do it right now is by using the Format Painter on each individual...

Controlling excel formula exists in multiple worksheets from one sheet

excel,excel-formula,excel-2010
I have multiple worksheets having same format but different data. Each has same formula at the same position in each worksheet. For example, the cell D10 of each worksheet has one formula to calculate square of the value located in D9 (=D9^2). Say, I have 100 worksheets and need to...

Compare two cells based on their color

excel,excel-vba,excel-formula,excel-2010
I want to compare two cells based upon their color they are having. I created the following function. If the two colors in a cell match then it should write Same and color the cell green in the cell, else 'Change' and color it red. However, I am currently getting:...

Excel with AND, IF, MAX

excel,if-statement,excel-formula,max,excel-2010
I can't quit figure out what I am doing wrong here, I know how to do the formula in multiple steps but can not figure out how to do it in one step all together. So what I am trying to do here is in column A there is numbers...

Excel: Returning value after checking column, using this column to define range for MIN, using MIN(value) to retrieve column 'name'

excel,excel-2010
For a business 'tool' in excel I need to do retrieve a location corresponding to a postcode. The location is found by first looking up the postcode in a column, checking the min value in the corresponding row and returning the location based on which column this MIN value is...

VBA - Outlook Task Creation - Recipient based on Dynamic Range

excel,vba,excel-vba,excel-2010
As of right now, the below function works, however I need to change the Recipient.Add field to the corresponding email address, with each change. All of my email address are listed in a column on the worksheet, and ideally I would like the function to just automatically add the correct...

Object required error '424' when iterating over Collection

excel,vba,excel-vba,excel-2010,excel-2007
When I run my function, I get the following error: Run-Time error '424': Object Required And it highlights the following line of code: For Each n In DataSet Here is my full code: '==============================|Find_Occurrences FUNCTION|==============================' ' Given a collection and a search value, return the number of occurrences of the...

Compare Worksheets

excel,excel-2010
I have two Worksheets (WS) in one Excel document: WS1 FIRST NAME LAST NAME WS2 FIRST NAME LAST NAME ID I'm looking to add a column in WS1 named MATCHED ID. MATCHED ID would display (below pseudo code result): (WS1:FIRST NAME + WS1:LAST NAME) COMPARE AGAINST ALL WS2 (WS2:FIRST NAME...

I have a list of email addresses in excel that i need to send emails to. The subject and body are in cells besides the email address

excel-vba,excel-2010,outlook-vba
As mentioned in the subject of this post, I am attempting to send emails automatically by running a macro so that if cell J2 has the words "Send Reminder" in it, then the email address in cell K2 should be sent an email with the subject title in cell L2...

Excel Not Transferring Data to Access Database

excel,excel-vba,ms-access,ms-access-2010,excel-2010
I have a table in in Access 2010 that stores project information. The main columns - for this purpose - are LocationID, Priority1, and Priority2 (which have proper names but for generalization...) I also have an Excel 2010 spreadsheet that has this information to be exported into Access. What it...

Macro to copy and paste from one worksheet to another

excel,excel-vba,excel-2010
I have a workbook where I need to be able to click on a single cell of a worksheet and hit my command button. That copies and pastes the cell value to the first blank cell in column E on a different worksheet within the same workbook. When I just...

how to pull a column named “NUMBER” from Oracle through Ms Query in Excel

sql,excel,oracle,excel-2010
I know, I know...The column shouldn't be named "NUMBER", but it was here before I was, and I can't change it for now. At the moment, I only have read access to this database, and I was told it would be changed...soonish... I've tried referencing it as Table."NUMBER" and that...

Multiple same values in Column A, need to find the greater date in column B

excel,if-statement,max,excel-2010,array-formulas
I'm stumped on a particular problem with some data that's imported to me. I have zero control over how the data is coming in. (Just to clarify that point). I have two columns and 107,000 rows. Column A has an ID#, Column B has the corresponding Date. The issue I...

Excel 2010 - filter pivot table by pattern

excel,excel-2010,ssas,pivot-table
in Excel 2010 I am trying to analyze some data from an external analysis service. In a pivot table I am trying to filter the report by one field which has multiple values separated by a comma. These look like this: AB, CD1, EF1-5 AB, CD1,3, EF1 BCD, EFG EXG,...

Create a graph in Excel 2010 using 3 columns of data

excel,graph,datatable,excel-2010
I have been looking online tirelessly for a solution to the following problem but to no avail. I am trying to create a seasonal plot for the table below. My goal is to plot sales against quarter, classified by year. Any help would be much appreciated! Thank you....

is it possible to edit .xlsm files using NPOI with C#

c#,visual-studio-2013,excel-2010,npoi
Thanks for the help from people on this site, I was able to modify .xlsx file using NPOI. Code is below, but my final goal is to be able to modify .xlsm file cell value. Is there a way to do this? I tried to run with the following code...

Split delimited 2nd and 3rd column data into new rows

excel,vba,excel-vba,excel-2010
I have the following table ID. ID2. String 123. 567, 986 ABC;BCD;ACD 142. 134, 654,1134 AA;BB I want it to be displayed ID ID2 String 123 567 ABC 123 986 BCD 123 ACD 142 134 AA 142 654 bb 142 1134 The values in the ID column are unique. Is...

Appyling one formula to excel table

excel,excel-vba,excel-formula,excel-2010,spreadsheet
I have the following table: I would like to calculate the interest rate that I would get for all combinations in this table. The formula I use is for example: =$C$2 + $C$2*B3 Please note, that this is just an example. My real table is much larger and doing it...

Delete all rows below the last row of data for import process from Access

excel,vba,excel-2010
Microsoft Excel 2010: From month to month, the number of lines of data can be variable. When I paste new data into the ILS_IMPORT tab, there may be 3,500 records and the next month could be 2,500. When I go to import the data into Access, and extra 1,000 lines...

Custom User Function (UDF) and Dynamic Ranges

excel,vba,excel-vba,excel-2010
I have a Custom User Function (UDF) that returns an array. I use it in an Array Formula on a large range of cells. The length of the returned array is dependent on the function parameters. This works great except one thing: When the length of the returned array is...

De-Stacking columns in Excel using VBA

excel,vba,excel-vba,excel-2010,excel-2007
I have a dataset in three columns composed of a repeating set of UUIDs in the first column, string responses for each UUID in the second, and a code for each response in the third. I need to break this out into multiple sets of columns, one for each repeating...

Excel - consolidate with OR operation

excel,excel-formula,excel-2010,array-formulas,consolidation
I have in a sheet a table like so: +------+---------+ | Name | Boolean | +------+---------+ | A | true | | B | true | | B | false | | C | false | | C | false | | A | false | +------+---------+ But in another...

Opening Outlook address book from Excel

excel,vba,excel-vba,outlook,excel-2010
I'm using VBA in Excel 2010, with Outlook 2010 (already open). How could I write a sub for a command button, such that when the user clicks on it: 1 Outlook address book opens; 2 The user selects a contact and clicks ok; 3 The contact's first name, last name...

How to run different VBA macros on selected work sheets

excel,vba,excel-vba,excel-2010
I have a workbook with different worksheets. I am having macros for different worksheets. I have a macro for all worksheets starting with SOD, another macro for worksheets starting with DMNE, another macro for worksheets starting with SAS. I have one macro for Usage tracking. My requirement is when I...

Regex - Link formatting

regex,excel,excel-2010,spreadsheet
I have the following link in excel: http://en.wikipedia.org/wiki/Melon_Bicycles I would like to retrieve the link as: http://en.wikipedia.org In google doc I used: =REGEXEXTRACT(A1; "https?:\/\/(.[^\/]+)"), whereas A1 is the cell with the unformated link. Any recommendation how to implement that in excel 2010?...

Cycle through each column in a row by name in a table

vba,excel-vba,excel-2010
I have a table with seven columns containing dates. Each column has dates that are assigned using a different formula for each column. My ultimate goal is to compare the dates against today, and if it's within a certain amount of time, to fire off an email, and then update...

Excel CustomTaskPane with WebBrowser control - keyboard/focus issues

c#,excel,winforms,excel-2010,vsto
I am having this exact issue https://social.msdn.microsoft.com/Forums/vstudio/en-US/e417e686-032c-4324-b778-fef66c7687cd/excel-customtaskpane-with-webbrowser-control-keyboardfocus-issues?forum=vsto Also mentioned here https://connect.microsoft.com/VisualStudio/feedback/details/521465/the-focus-issue-between-excel-cells-and-excel-customtaskpane-with-webbrowser-control I am writing an Excel 2010 plugin using Visual Studio Professional 2013. I've created a simple CustomTaskPane with a System.Windows.Forms.WebBrowser child filing...

Extract 5-digit number from one column to another

vba,excel-vba,excel-formula,excel-2010,excel-udf
I need help with extracting 5-digit numbers only from one column to another in Excel 2010. These numbers can be in any position of the string (beginning of the string, anywhere in the middle, or at the end). They can be within brackets or quotes like: (15478) or "15478" or...

Dealing with Run-time error '9': Subscript out of range when iterating over Worksheet

excel,vba,excel-vba,excel-2010
Not an Excel/VB expert but I keep getting Run time error: 9: Subscript out of range The error is occurring at the For Each line. Not sure why. I'm trying to copy worksheets from one workbook to another workbook. The Workbook strFileName is being open successfully and the workbook does...

Excel 2010 VBA Can't get Sub Menus to display

vba,excel-vba,excel-2010,submenu
I have the following code that builds a custom menu in Excel. Works well. I'm trying to modify it to use sub menus. It will add the menu item for East Options and West Options. I'm trying to modify the East and West # 1 items so they appear as...

VBA Join Ranges

excel,vba,excel-vba,excel-2010
So I have a constant 7 number of ranges which are all one cell wide and a large, arbitrary height which is the same between them. Is it possible to join the ranges so that I have one range that only consists of all 7 ranges? I have tried the...

How to bulk-import all Power Query workbook connections into PowerPivot?

excel,excel-2010,powerpivot,powerquery,powerbi
Within PowerPivot for Excel, under the 'Design' tab, there's an option to use 'Existing Connections' as a data source for Power Pivot. Here we can select our carefully shaped & managed Power Query connections (e.g. to SQL Server, Web pages, flat files). However this interface only permits the selection of...

Find Cell's Column Given String

excel,vba,excel-vba,excel-2010
I am trying to find the column of a cell which contains the string "Status". The cell is in a different sheet named "Report" and I do not know where the cell is; I only know exactly what it contains. All I need to know is the column that the...

Logical operations using Named Ranges within Excel

excel,excel-2010,boolean-logic,named-ranges
I came across this issue when trying to use better named referenced in my spreadsheet Take this example using Boolean logic Column A has the named range "Include1" Column B has the named range "Include2" The formulas are: What I see is that if I use the named range directly...

Excel: Adding a regression line into an existing graph with multiple lines

excel,excel-2010
I'm having a line graph of production waste corresponding to four different production sites. However, I want to include a trend line (regression) in the same graph. This regression should conduct a trend line of the total production waste of the production sites. I.e. if production site 1 through 4...

Converting Date Into a specific text format in excel

excel,excel-formula,excel-2010,excel-2013
Ok so i have been trying to do this and i pretty much keep on failing :p Basically what i want to do is take the date 10/1/2013 and turn it into 201310. I tried to concatenate using left and right. tried to make it into text using left and...

All Possible combinations of data in multiple columns separated by a delimiter [closed]

excel,vba,excel-vba,excel-2010
I was able to perform a Cartesian product on multiple columns which have a single value in every cell. However, I was wondering if there is a way I could transform a Unflattened Data separated by delimiter into a Cartesian product For example ID ID2 String String2 123 123;1234;1234 Sample;...

how to add a value to cell

excel,vba,excel-vba,excel-2010
I am using the spreadsheet to do inventory- scanning parts in bin I am trying to add a A2 Value next to the (Target.Row, 5)(row 6) how do I make A2 or A9 or the next scan Value show up on Column F so if column E & F don't...

Search for a specific number (in inch) where the number isn't part of a larger expression

excel,excel-formula,excel-2010
I want to use an Excel formula that returns the correct index in the cell when the text in the cell contains the term '2"' (two inch). This is possible with the search function. The catch is that I only want to find instances where it's actually '2"', not cases...

Unable to extend VLOOKUP formula

excel,excel-2010
A quick question on VLOOKUP. I have two sheets ("Sheet2" acting as source or list of elements and "Sheet1" is where the VLOOKUP formula will be used) I have created a name so that I can reuse the vlookup formula for A2 (Sheet1) also. The issue is when I drag...

Paste cell values rather than formulas

excel,excel-vba,excel-2010
I am searching multiple sheets for a string, once this string is found it copies it to the MergedData sheet. The problem i am having is it is coping the formulas rather that the value. I have been searching for hours and cannot seem to find a solution. Here is...

Excel Sheet Protection Stopping Macro

excel,vba,excel-vba,excel-2010
I have been using a piece of code that zooms to fit the the worksheet to the active window. However, if I add sheet protection the macro will fail. To get around this problem, I call a sub to remove the protection then reapply the protection afterwards. Is there a...

Convert vertical reference to horizontal reference

excel,excel-formula,excel-2010
I have the problem that I have horizontal input data and I would like to use selected cells, which are in a vertical format, and for example simply calculate two vertical cells. At the moment I am typing in every cell by hand, because the locking of the colum works...

Four Columns Match on Different Worksheet

excel,excel-2010,vlookup
Is there a way to find if the combination of 4 column cell values has a match with a different sheet's 4 column cell and display true if the match exists using vlookup? If not what would be the best way to deal with this situation?

.copychart from Excel to Word errors unless both applications are visible

vba,excel-vba,excel-2010,word-vba,word-2010
Using .copychart in pasting from Excel to Word errors in my code below. If both applications are visible, as I've had them while programming, then it works well. Is there a way to hide the applications (let them run in the background) and still use .copychart or do I need...

Run-Time error on add method VBA

excel,vba,excel-vba,excel-2010
My function's intent is explained in the docstring. However, when I run it, I get a "Run-Time error '91': Object variable or With block variable not set", and a line of code is highlighted, as indicated below. I cannot find the source of this issue, especially since I am rather...

Excel INDEX isn't working with Table reference

excel,excel-formula,excel-2010
I am using Excel from Office 2010. I am trying to get values from a specific row (or, eventually, rows) in the table, found by running a search. I can successfully identify the row in the table that I want to output the data from, but the INDEX function is...

how to set the decimal separator for printing to file different from the system setting

excel,vba,excel-vba,excel-2010
Using VBA in Excel 2010 I want to print the contents of a cell in a worksheet which is a decimal number to a file. I am using the ´Print #´ statement for this. I am working in an environment where the comma ´,´ is the system wide decimal separator....

Userform to open on only specific sheets

excel,vba,excel-vba,excel-2010
I am trying to create a workbook which is for parcel data, the sheets on the work book are specific to the weeks in the year so i have 52 sheets for the weeks named WK 1, Wk 2, Wk 3, etc etc.. and another sheet called "Front" which is...

Sort entire Pivot table in Excel 2013 by column

sorting,excel-2010,pivot-table,excel-2013,powerpivot
I have a Pivot Table with many columns. I want the Pivot the ability to sort one of the columns in a way that the whole column is sorted and not the relative position in the hierarchy. Example: NAME PRODUCT SUM Joe A 400 Joe B 200 Joe B 300...

EXCEL Reference a cell in the previous visible row

excel,excel-vba,excel-formula,excel-2010
How can I reference a cell in the same column in the previous visible row from a filtered range?

Excel lookup by text

excel,excel-2010,excel-2007
I am trying to make lookup for excel table using the text in the columns For example if i have A1 | A2 BMW-X6 | 1 BMW-X5 | 1 Mazda-1 | 2 Mazda-2 | 2 Mazda-3 | 2 Mercedes-Benz | 3 Mercedes- cls | 3 I want where there is...

VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Nested IF Excel formula

excel,excel-formula,excel-2010
I am currently using the following formula i.e. =IF(COUNTIF($A$1:A2,A2)>4,A2+1,A2) to change the number when I drag this formula downsdie of the rows. For Example: in this case for every five rows number will change i.e. A1 to A5 it will 1 and A6 to A10 it will be 2 and...

Dynamic Summing Range

excel,vba,sum,excel-2010
Currently I have a medical spread-sheet with a list of clients that we have serviced. We have 8 different clinical categories which are denoted by different acronyms - HV,SV,CV,WV,CC,OV,TS and GS. A client can receive multiple therapies i.e. HV,SV,CV - in the background we have a counter mechanism which would...

Check Excel File Sheet for missing columns

vb.net,excel,ssis,excel-2010,vb.net-2010
I have an ssis package which takes an excel file and imports it, but if any of the columns in the sheet are missing then the import fails. Im trying to write some code to check the column header in a sheet to make sure it contains a set list...

How do you include the number of rows in an Excel 2010 worksheet name?

excel,excel-vba,excel-2010
Is it possible to include the number of rows in an Excel worksheet (that contain data) in the worksheet name? Currently the names look like "Widgets", I would like them to look like: "Widgets (n)" The first row is a header, so the total number of rows would be n-1....

MS Excel 2010 - VBA to lookup in one column a customer number and Tag the corresponding column with Yes or No

excel,vba,excel-vba,excel-2010
I have an extremely large dataset with customer numbers and we cannot just use a =IF(E3=160248, "YES", "NO") to tag a particular customer number of 160248 with YES or NO. Instead, I would like to use VBA code to lookup Customer_Number in column E and return a YES or NO...

unable to use MIN and IF function in excel

excel,excel-formula,excel-2010
Age Qualification 19 Higher Secondary School 24 Graduate 25 Post Graduate or above 21 Graduate 17 Higher Secondary School I am trying to get Minimum age of graduate student which is 21. The incorrect formula i used is =MIN(IF($B$2:$B$6="Graduate",$A$2:$A$6)) which gives 17 as min value....

How do I sum results of two if statements in the same cell?

excel,if-statement,excel-formula,excel-2010,pivot-table
I'm looking to have the results of two If statements calculated and added in the same cell. I'm getting #VALUE! error. =IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29)),"",GETPIVOTDATA("Sum of CHARGES",'Ship City'!$A$3,"ship_city",$B$7,"carrier_type",$A$27,"INV_month_id",D$6,"INV_year_id",$D$5,"Company Name",$B29))+IF(ISERROR(GETPIVOTDATA("Sum of CHARGES",'Recipient...

Can I use a variable to define a Range in the Range(“X:X”) format

excel-vba,excel-2010
I currently have a formula which checks for: If Intersect(Target, Range("J:L")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub I need L to be a variable, the 2nd part of the range is essentially J+ an Offset. I've used L here and it works however I need it to...

Connecting two path strings to get the final path?

excel-vba,excel-2010,vb.net-2010
I'm trying to save excel file into a specific path. So basically, when I click the button, I'm creating a folder, and want to save the file inside that folder. The created folder has the current month as name. I'm trying to save into that current month folder. 'Create folder...

Excel VBA Loop column and copy data

excel,vba,excel-vba,excel-2010
I need some help with a bit of code i'm trying to write. I am not very experienced in VBA, I thought a VLookup is what i needed but after thinking about it. I think a Find loop with work even better. There will be a button click on sheet1....

How to divide long rows into multiple smaller rows for an entire (very large) data spreadsheet

vba,excel-vba,excel-formula,excel-2010
Google Doc with data in its current format followed by desired format: https://docs.google.com/spreadsheets/d/1XlxEVcP6QpWYyOeflLmp_mKflCBclim_UQSeMkHByh8/edit?usp=sharing I am trying to create a template to rearrange a data set that is exported in a horrible format. I have posted a link to a Google Doc that has an example of the data in its...

Subroutine not called from worksheet_change event

excel,vba,excel-vba,events,excel-2010
By changing a value in a cell a call to a macro/module ) is made. When running this macro from the ribbon or Alt + F8 it works perfectly. Why does the call from the first macro not work? The Macro behind Sheet1 Private Sub Worksheet_Change(ByVal Target As Range) If...

Wrong cell when conditional formatting with values

excel,excel-2010,conditional-formatting
I regularly encounter an issue on Excel 2010 when I create a new rule using a formula. The issue is reproducible on several of my computers. I select several cells (for instance A4:B143) and then create a new rule using one of the following formulas: =$A4="issue" =($A4="issue") then press Enter....

Runtime error 1004. Need to fetch value from another sheet in same Excel file

excel-vba,excel-formula,excel-2010,vlookup,worksheet-function
Sub UpdateFormula() Dim CurrStr As String Dim EndRow As Long On Error GoTo 0 EndRow = Range("A" & Rows.Count).End(xlUp).Row BaseStr = UCase(Range("A2").Value) Application.ScreenUpdating = False For iter = 4332 To EndRow CurrStr = UCase(Range("A" & iter).Value) result = Application.WorksheetFunction.VLookup(CurrStr, Sheets("CustAR").Range("A2:A2499"), 1, False) '=IF(ISERROR(VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)),"NotFound",VLOOKUP(A2, CustAR!$A$2:$A2499, 1, FALSE)) Next...

Display pivot table filter values

vba,excel-vba,excel-2010,pivot-table
I have a pivot table where I have applied a date filter: I am looking for a way to display the filter information in a cell. e.g. between 1/1/2015 and 10/3/2015 I have tried the following to just get it to display the information in a message box: Sub DisplayRange()...

Change chart font using VBA

excel,vba,excel-vba,excel-2010,excel-charts
How do I change the font of an Excel chart using VBA? If I manually select the chart, and record a macro while I manually change the font name and size, I get the macro below. But when I immediately replay the macro, it throws a run-time error: "The specified...

Excel 2010 VBA: How to export the current worksheet asking user where to save?

excel,vba,excel-vba,excel-2010
I've a .xlsm with a big customized toolbar full of custom tools for handling common situations at work. The problem is that I need often to save the result and then send to other users, but I don't want to preserve macros / customized toolbars into saved Workbook. So I'm...

VBA Acces Database - Clean up code by the use of a loop with 2 variables

arrays,vba,import,ms-access-2010,excel-2010
I'm trying to write a code that imports 4 different files in 4 different databases. I wonder if there is a way to make this much shorter and simpler by using a loop? I tried one, but I can't figure out how to direct one file to a different database....

Separating columns into multiple columns excel2010

excel-vba,excel-2010
What I need to do: I have two columns in excel, (x) and (y) essentially There are multiple data sets continuing down columns A & B (it ranges, but the one I've been working on goes down to about ~80,000 and has roughly 8 data sets; the full data set...

Excel #Value! error - but correct formula

excel,excel-2010
I have an Excel 2010 workbook with this formula: =EOMONTH("01"&TEXT(B7,"MMM")&IF(MONTH(CMVAR)<4,TEXT(YEAR(CMVAR)-1,"YYYY"),TEXT(YEAR(CMVAR),"YYYY")),0) It resolves when you are in the cell and press Enter, however when the workbook first opens or is refreshed, the result is #VALUE!. Here are the components: B7 =IF(OR(MONTH(CMVAR)>6,MONTH(CMVAR)<4),"Apr",IF(MONTH(CMVAR)=4,TEXT(EDATE(CMVAR,-3),"MMM"),IF(MONTH(CMVAR)=5,TEXT(EDATE(CMVAR,-3),"MMM"),TEXT(EDATE(CMVAR,-3),"MMM")))) which equates to Apr. CMVAR 31/03/2015 The formula...

PowerQuery multiple files and add column

excel-2010,powerquery
I have the inline PowerQuery to automate my weekly reporting. Since I am new to power query I followed up this tutorial and try to add a custom column so I can use it to see week over week improvements, the thing is that the column that is added is...

excel vba macro to match cells from two different workbooks and copy and paste accordingly

excel,vba,excel-vba,excel-2010
i have 2 workbooks, workbook A and workbook B. Each workbook has a table. workbook A has 2 columns. All three columns are filled. product_id Machine_number and Workbook B has the same 2 columns but only one column, Product_id, is filled. The other 1 column is vacant. I need to...

VBA in Excel to search for string over multiple tabs and return locations

excel,excel-vba,excel-2010
I have formed the code to do the searching the problem is trying to get it to find the multiple results. Currently it will return the first location of the string on each tab but then moves on. When I implement the While loop currently commented out it appears to...

My macro freezes after an undefined amount of iterations

excel,vba,excel-vba,excel-2010
I have a macro that checks if some names on column Q appear on column A (which is ordered alphabetically) and prints them out on column S if they do. However, every time I run it it freezes after an undefined amount of iterations (never on the same amount of...

Excel Conditional Formatting Data Bars based on Color

excel,excel-vba,excel-2010,conditional-formatting
I cannot find a way to change the color of an Excel data bar based on value. Current formatting options only permit different colors based on positive/negative values. I'm currently using Excel 2010. I would like to have the color of a data bar show up as 'red' if the...

Excel horizontal axis values irregular and ugly

excel,excel-2010
The image shows a chart with time vs temp and humidity data from a high-powered rocket launch (it hit about 7000 ft at the highest point, and then slowly parachuted down). There are about 4000 data points here. What I want to fix is the ugly horizontal axis numbers. How...

VBA how to initialize vCPath

vba,excel-vba,excel-2010
How do I initialize vCPath? VBA Run-Time Error 1004 @Garry's Student says I "must somehow use the info you get from Application.FileDialog(msoFileDialogOpen) to generate the full filespec of the file you wish to open". What's the simplest way to do this? I am a VBA beginner: I have been programming...

Ignore hidden columns in SUM(x:y), like AGGREGATE can ignore hidden rows

excel,excel-formula,excel-2010
I know there is a similar answer here: Ignoring a hidden column in an excel sum formula ...and it's a good one, but it isn't ideal for me. I really just need a simple function I can apply dynamically. Is there a way to do this in a similar way...

Excel: Count if contains “=”

excel-formula,excel-2010
I've got a column that consists of about a thousand of "+","-" and "=" signs. I need to check which symbol appears the most times as a part of a bigger formula, but excel doesn't seem to count the "=" signs correctly. The following formula returns count of blank fields...