FAQ Database Discussion Community


Copy/Paste dynamic range

vba,excel-vba,offset,excel-2013
Starting from Sheet "DATA" range B4:Hx, where x is my last row taking by a row count. I need to copy this range and paste it as values on sheet "bat" starting at A1. Going forward I need to offset columns in 6. So my second copy will be I4:Ox...

Excel - How to concatenate 2 values to make a refference

excel,excel-formula,excel-2013
I have a long column of data (15000 values) that simplified looks like this: A B C D 1 lorem pellen Vestibulum 2 epsum tesque pretium 3 Morbi vel convallis 4 fermentum tellus nibh 5 Interdum molestie Vi .. 15000 Then I have a second table: A B C TYPE...

How can I get data from a Table [closed]

excel,excel-2013,data-validation
Can someone assist me on how can I get data from a table in Excel 2013? I have a table named Personal: and I want to have a control in a cell like this: (I do not know how to add this control in a cell) So I can select...

Identifying Context Filter in DAX Measure

excel-2013,dax
How do I create a DAX measure in Power Pivot that shows the column name that is used as the lowest level filter context for each row in the Pivot Table? I have 2 tables which are related on the Account column. Table DimAccount Account,Model_L1,Model_L2,Model_L3,Business_L1,Business_L2,Business_L3 1,Working Capital,Trade Payables,Trade Creditors,Opex,Technology,Managed Services...

Quickly Add New Series (Column) to Existing Graph - Excel 2013

excel,excel-2013
I have an existing graph, let's say with 2 series against the x-axis. In my data table I've just added a new, 3rd column that I want to map to this existing graph. How do I do it quickly? I tried copying/pasting the data column into the graph, didn't work....

Hyperlink Screen Tip

excel-vba,excel-2013
I'm using the code below to create a list of filenames from a given folder. Public Sub ListFilesInFolder(SourceFolder As Scripting.folder, IncludeSubfolders As Boolean) Dim fName As String Dim Lastrow As Long On Error Resume Next For Each FileItem In SourceFolder.Files ' display file properties Cells(iRow, 3).Formula = FileItem.Name Cells(iRow, 4).Formula...

How to Open and Parse Excel Data?

c#,excel-2013
(Alternate Title: ReadAllLines Analogue for Excel?: What's the Best Way to Load and Manipulate Excel Data?) I would like to quickly crack open an excel sheet and perform text manipulations. I want the operation to work like ReadAllLines (https://msdn.microsoft.com/en-us/library/s2tte0y1(v=vs.110).aspx) but for Excel. I found the following question which is on...

repeating a formula conditionally in excel 2013?

excel,excel-formula,excel-2013
I am trying to calculate a formula and then dragging it to apply it for the whole column but the problem is that I want to compare first cell of the column A with the first cell of the column B and then second cell of the column A with...

Loop through directory, open workbook, print selected worksheets

excel,vba,excel-vba,excel-2013
I have about 50 workbooks that have 3 worksheets, and I need to print the first 2. The names of the worksheets will be constant so this shouldn't be too hard. I wrote the following in VBA and it doesn't error out but my printer doesn't print anything. What am...

Array Formula for text search

excel,excel-formula,excel-2013
I have used this array formula to find true/false. The formula is only considering the text in the first cell {=isnumber(search(G2:G7,A1))} ...

excel Calendar and HYPERLINK

excel,excel-formula,excel-2013
I want to make a calendar and when I click on cell (date) I want that it link me to different sheet and cell =Days+DATE(CalYear;MONTH($B$9);1)-WEEKDAY(DATE(CalYear;MONTH($B$9);1);(WeekStart="Monday")+1)+$A12*7-6) =HYPERLINK("#"&CELL("address";Sheet2!D1);Name of the cell) I want to combine them together. =HYPERLINK("#"&CELL("address";Sheet2!D1);Days+DATE(CalYear;MONTH($B$9);1)-WEEKDAY(DATE(CalYear;MONTH($B$9);1);(WeekStart="Monday")+1)+$A12*7-6)) Is it possible?...

Conditional Formatting for Filtered area

excel-2013,conditional-formatting
I currently use the following formula in conditional formatting to highlight ever other row. =IF(ISBLANK(A1),"",MOD(ROW(),2)=0) However I would like to find a way to modify this to only apply to currently visible section of a filtered dataset. So for instance the above currently highlights every even row but if I...

VBA Copy Destination but as values

excel,vba,excel-vba,excel-2007,excel-2013
I'm trying to transfer data from one document to a different document using "Copy Destination" as I want to avoid using the clipboard but I want it to stop taking the formatting with it... Dim Sheet As Worksheet Dim FoundLocationSheet As Boolean Dim n As Long Dim AllSheet As Worksheet...

Excel MATCH function partially working

excel,match,excel-2013
I'm using the Excel MATCH function, and it works if the lookup_value is an unmodified number, but it does not always work if the lookup_value is a calculated number. To be more specific: My spreadsheet can ask the user whether or not she would like to use a recommended value...

Cancelled Save De-activates Links

excel-vba,excel-2013
I've put a script together which extracts a list of files from folders and subfolders from a given path. In column B there is a unique, incremental ID created for each file found. This ID is formatted as a hyperlink. When the user clicks on the hyperlink, it opens a...

Excel 2013: copy the row for the number of computers in that room

excel,vba,excel-vba,copy-paste,excel-2013
I have a worksheet in my workbook that has the number of computers by room that I want to copy the row for the number of computers in that room. I have several rooms that I need to do this for. Example: Room Count Model Year 201 24 MBP 2015...

Can Not Get My VLookUp In Excel To Return The Requested Data

excel-formula,vlookup,excel-2013
Can Not Get My VLookUp In Excel To Return The Requested Data I am trying to pull data from another sheet based on data selected from a dropdown on the main sheet. All the formatting is "General" =VLOOKUP(F15737,'Location Master'!$A:$J,2,FALSE) It just keeps returning me #N/A...

Invalidate ribbon control in Excel 2013 for multiple workbooks

vb.net,ribbon,excel-2013,excel-dna
I have a customized ribbon where I added a tab containing a toggle button. This toggle button has a getPressed attribute which is linked to a callback function returning the pressed state of the toggle button. The purpose of the toggle button is to display/hide a custom task pane. This...

Autohotkey F4 script does not work on Excel 2013

excel,autohotkey,excel-2013
I'm trying to build a very simple script for Autohotkey that binds the F4 key to a specific combinantion of keys (Win+4). It works perfectly on all applications but one: Excel 2013. It even works on Excel 2010, but not on Excel 2013. Mapping F2 to Win+2 works just fine,...

Get code runtimes via GetTickCount and format it in specific ways

excel,vba,excel-vba,excel-2007,excel-2013
I'm trying to make VBA's GetTickCount work so that I can see the runtime of code, but it doesn't have to be super accurate. The following bit of code works good but I need a few changes and can't work out how to achieve this. #If Win64 Then Public Declare...

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

COUNTIF with OR Statement

excel,excel-formula,worksheet-function,excel-2013,countif
I'm trying to count the number of times two statements occur. The problem is that if the first statement occurs then it won't check for the second. Maybe if easier if I explain like this: If statement1= true then count = 1 and move next else if statement2= true count...

VBA Open URL's in Excel 2013, 64bit

excel,vba,excel-vba,excel-2013
I'm trying to open links via vba but I'm having lots of problems, here is what I was using previously: Sub OpenUrl() With Application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual .DisplayAlerts = False End With Dim lSuccess As Long Dim LastRow as Long LastRow = Range("A65536").End(xlUp).Row For...

Creating a “color scale” using vba (avoiding conditional formatting)

excel,vba,excel-vba,excel-2007,excel-2013
I'm looking for a way to apply a color scale to a set of cells via VBA code but not by applying some conditional formatting... I want to apply them as static colors (InteriorColor) I've searched plenty of excel sites, google and stackoverflow and found nothing :( For my situation...

Fix for Windows 10 Excel 2013 Build 9926 Find and Replace freeze bug

excel-2013,windows-10
Does anyone have a better fix for the following bug I am getting in Excel 2013 on Windows 10 Enterprise Technical Preview Build 9926. The issue occurs where trying to run a ‘Find & Replace’. 1. Open a new workbook 2. Type ‘Test’ into Cell A1 3. Press ‘Ctrl+F’ 4....

Create Bar Chart in Excel with Start Time and Duration

excel,time,charts,excel-2013
I want to create what is essentially a Gantt chart in Excel 2013 with time of day as the x-axis, date as the y-axis, and floating bars representing time durations. Example data: Date Start End Duration 21-Apr 6:45 8:20 1:35 21-Apr 10:00 11:30 1:30 21-Apr 14:00 16:00 2:00 22-Apr 6:50...

VBA retrieve hyperlink target sheet?

excel,vba,excel-vba,hyperlink,excel-2013
I'm trying to retrieve the sheet reference location from a hyperlink that's in a cell The following doesn't seem to work as test doesn't return anything, even though G8 points to Sheet: KO in Cell A19 test = Range("G8").Hyperlinks(3).Address Now if I run the following: For Each hl In Sheets("LIST").Hyperlinks...

Excel VBA: User defined function based on checkbox input not recalculating

excel,vba,excel-vba,checkbox,excel-2013
In Excel 2013, I have defined a custom function CUSTOM_EQUITY which returns "EXTERNAL" or "INTERNAL" based on whether a checkbox (form control) is checked or not. It works okay when I press enter, but it doesn't auto-update when I click the checkbox, checking or unchecking it. Forcing the update with...

Pulling data out of an worksheet by country

excel,excel-formula,excel-2013
I have a huge amount of people in my excel sheet and I want to split them by country with excel coding, here is an example of my data: Country | Name UK | Tom Austria | Bobsky UK | Ralf Germany | Badolf Germany | Schwartz UK | Andy...

Align Excel cell to center VB - xlCenter is not declared

vb.net,excel,excel-vba,ms-access-2013,excel-2013
Im using Visual Studio 2013 Visual Basic, MS ACCESS 2013, EXCEL 2013 My program Save As the data in my datagrid to excel. I use access 2013 as my database Here is my code: Imports System.Data.OleDb Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Form1_Load(sender As Object, e As...

Excel add traffic light

c#,excel,excel-2013,conditional-formatting
I create a Excel file based on a DataSet, I want to add a column with a traffic light. The traffic light: int trafficCollor = 1; if (trafficCollor == 1) { DataRow NR = DT.NewRow(); NR[0] = ""; //add string with the code for the Excel traffic light yellow }...

Object Defined error in Excel VBA script

excel,vba,excel-vba,excel-2013
I'm trying to build a script that extracts the first 6 characters of a column (user defined) and either inserts a new column and pastes those results or just pasts those results over an already existing column (User's choice) but I keep getting an object define error (I've marked the...

Taking data from other workbooks which vary in name - asking user to select them

excel,vba,excel-2013
So I found this amazing answer on a previous question and I was just wondering how I could start the search window at a specified location - Saves them having to search though multiple folders every time. Sorry I couldn't just reply to the original post, I have 1 reputation...

Range in .Select() not working on xlApp.ActiveWindow.FreezePanes

vb.net,excel,excel-vba,excel-interop,excel-2013
I just want to select A1 to A3 to freeze pane. But xlApp.ActiveWindow.FreezePanes freezes A1 to A10. I was experimenting for 3 hours is this line of code for 3 hours: xlApp.Range("A1:A3").Select() But still A1 to A10 are the cells that are freeze. I just want to freeze A1 to...

How to pass a list of sheets to a VB Sheets.Select

excel-vba,excel-2013
I'm trying to select specific sheets in an workbook. I have a formula that calculates the specific sheets that need to be printed and then creates a quoted comma delimited list i.e. "Page 1", "Page 5", "Page 18" Now I'm trying to pass this to a select to have it...

How to turn a range value to a double (VBA)

vba,excel-vba,excel-2013
So I want to make a function that takes a value in a cell to make some calculations, but whenever I try to get the value from the cell rather than hard-code it I get a #VALUE! error whenever the function is run. Whereas this did not happen when the...

Use custom image for button in Ribbon Bar Excel

excel,vba,excel-vba,excel-2013
I have a few custom functions that I'd like to add to my custom tab and group in the ribbon bar. I'd like to use a custom image I created and took a snapshot of (.png). I've seen a few websites that explain how to do so, such as this...

Opening CSV File

excel,csv,ms-office,excel-2013
I am generating CSV files. My first row it is column names, and it looks like User ID;First Name;Last Name;Email;... But if I will change User ID to ID, MS office cannot open this csv and shows me error "Cannot read record(number of record)". But this file opening correct on,...

VLOOKUP not returning for datetime

excel,vlookup,excel-2013
I have a list of hours when water is used. this list does not generate hours when water was not used. I'll have list like: Hour Liters 5/3/14 6:00 PM 36.288 5/3/14 7:00 PM 15.328 5/3/14 8:00 PM 1.6 5/4/14 11:00 PM 18.752 5/5/14 12:00 AM 21.664 5/5/14 1:00 AM...

Object required error in Workbook_Open with For loop

excel,vba,excel-vba,excel-2013
***It's working!! Thanks everyone for the help :D - turns out range was declared and set as "columnnrange" and the for loop was using "columnrange". It's the little things ;) . Appreciate the insight and guidance all! *Thanks for the help everyone! I've updated the code per the responses. The...

VBA Error Handling to check for folder existence

excel-vba,excel-2013
I'm trying to implement some 'Error Handling' into an existing script and I've put together the following: Edited Code Private Sub Workbook_Open() Dim j As Integer Application.ScreenUpdating = False 'Display the splash form non-modally. Set frm = New frmSplash With frm .TaskDone = False .prgStatus.Value = 0 .StartUpPosition = 0...

VBA Centre Userform On Active Screen

excel-vba,excel-2013
I wonder whether someone can help me please. I'm using the 'Extract' code below which runs on the click of a button, which also, as you may be able to see, initalises a 'Splash' form with a scrolling progress bar. Private Sub btnFetchFiles_Click() Dim j As Integer 'Display the splash...

Push values from one sheet to another?

excel,excel-vba,excel-formula,excel-2013
I have one "Main sheet" where I want to view certain key values but I also want to add more/new data to the "Data sheet" through an update field with the press of a button. The cell I want to update is dynamic but I have the cell address in...

Multiple criteria conditional formatting in a PivotTable

excel,table,pivot-table,excel-2013,conditional-formatting
Can you apply conditional formatting comparing multiple criteria (e.g. two pivot table columns)? I have a PivotTable in Excel 2013 that compares variable percentage rates and their corresponding values. I want to apply conditional formatting to the PivotTable by highlighting each cell that is greater than the adjacent percentage rate...

delete blank rows on an excel worksheet, in a different excel workbook, using vba

excel,vba,excel-vba,excel-2013
I have 2 work books. Book_A and Book_B. I want to delete blank rows in Book_A worksheet1, from Book_B worksheet1. I have written a code using VBA to delete blank rows. Sub RemoveEmptyRows() ' this macro will remove all rows that contain no data Dim i As Long Dim LastRow...

Finding ID based on first and last name

excel-vba,lookup,excel-2013
I have a list that contains Employee ID in Col A, First Name in Col B, Last Name in Col C. I am needing to write a macro that gives a userform to input First and Last Name and from there it will put the proper Employee ID into the...

How to copy Excel text and keep line breaks

excel,copy-paste,excel-2013
I have an Excel (2013) spreadsheet that contains multiline text in some of the cells. Word-wrap is enabled for those cells, so they appear with line breaks correctly. However, the cells in question are protected (and I don't have the password), so I can't click into the edit box to...

Combining matrices to produce one matrix

sql,sql-server,excel,matrix,excel-2013
I have a requirement to build a normalised table of results from three table sets (currently in Excel 2013) as shown here: What I would usually do is manually run through each selection to create the normalised sets, but as you can see it's a bit complicated here. The table...

How do I count how many occurrences per week something happens excel?

excel,date,excel-2013
I am looking at a rather large excel spreadsheet with a long list of dates on it. Each one of these dates marks an error. What I am trying to find out is how many errors occur per week? From there I hope to make a control chart. Thanks so...

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

Target.Validation.Type results in “Application-defined or object-defined” error

excel,excel-vba,excel-2013,data-validation
I am trying to determine if a cell has data validation using this statement: If Target.Validation.Type = 3 Then However, I get an error in VBA: application-defined or object-defined error I tried using On error Resume Next and On error Goto 0 but it doesn't help. How do I check...

Dynamic formula with quotes inside a COUNTIFS function

excel,excel-formula,excel-2013,countif,excel-indirect
I am trying to adapt the following formula (counting instances of values between 0 and 24): =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,"<24") (formula evaluates to 75), to something like: =COUNTIFS(cancellations!AG2:AG408,">0",cancellations!AG2:AG408,INDIRECT("" & "<" & B1*24 & "")) but this evaluates to zero. (B1 = 1 in the above example.) When I view the INDIRECT function inside...

Activate worksheet using excel VBA

excel,vba,excel-vba,excel-2013
I have a file "Workbook A" in a folder. An updated version is sent to me every fortnight. I want to open this workbook from another workbook, "Workbook B" and at the same time delete blank rows in "Workbook A". The opening and deleting operations will occur through the use...

Check if cell contains Non-Alpha characters in Excel

excel-2013
Is there a Non-VBA way to check Col B and Col C to see if they contains any characters that are Non-Alpha? Just to clarify by Non-Alpha I mean anything not part of the alphabet(case insensitive). Col B and Col C is a list of First and Last Names. Some...

Detect autofilter on Excel Tables

excel,excel-vba,excel-2013
Below line detects if there are active AutoFilter in a sheet. Debug.Print Sheet1.AutoFilterMode This returns True if there are active filters in Sheet1, False otherwise. My problem is, this doesn't detect AutoFilter on Tables. Is there a way to detect AutoFilterMode on Tables, a property I might have missed?...

Conditional Excel formula based on existing value in adjacent cells?

excel,excel-formula,excel-2013
So I have an excel sheet. I want ColD to contain a formula that, when ColC to the left contains an "@" symbol, ColD will fill with a certain text. It would exclude Row1 as that contains headers. Not really sure how to do this, is it possible?...

VBA “Check if folder exists” works only when there is a file in the folder

vba,excel-vba,excel-2013
I got the original code from www.rondebruin.nl It is designed to test whether or not a folder already exists in the directory. I modified it to fit my needs and it seemed to work well. Today, i discovered that it only works properly if the folder being tested for is...