All Questions
Tagged with vba microsoft-excel-2013
135
questions
0
votes
1
answer
80
views
Excel 2013 fails to record macro correctly
So, i want to create a macro that will set selected cells' format to 0,0 ¥.
However, if you record the steps and then try to run the macro it will set format to 0,0 ?. Is there a way to fix this?
- 11
0
votes
1
answer
818
views
Conditional formatting for specific group of values/rows based on cell value(s)
I am having some data table that is separated in lets say "blocks" of rows. As you can see on the image, starting from Column D (A1) there are some values in first row, and then 5 rows empty ...
- 121
0
votes
0
answers
111
views
Excel: Dynamic change cell with Date/Time when any other cell is changed in the worksheet
I am trying to decypher a formula which will stamp auto Date/Time or some text when I change any random cell (more instances) inside some certain range (for instance A1:K45).
What I did is for one ...
- 121
0
votes
0
answers
39
views
How to replace formulas into values but with possibilty to remain referenced to its original source (dynamically)
I would like to have the possibility to convert some formula (in my case CONCAT) into values, but with the possibility to select those values and not to have formula shown, and also to be able to ...
- 121
1
vote
1
answer
2k
views
How to have unselectable cells and selectable uneditable cells?
In Excel cells are usually selectable and editable.
I can format some cells to be unlocked and then protect the worksheet with only the option to select unprotected cells.
That way the locked cells ...
- 163
0
votes
0
answers
900
views
Excel 2013 - Assign value to cell in Sheet2 based on a conditional in a cell in Sheet1
I would like to assign values to cells in one sheet based on the values of cells in another sheet. I have followed this answer, but it appears to work only if both the reference cell and the target ...
user1077898
0
votes
2
answers
73
views
Unable to Extract the Specific cell value against the lowest Priced item in a excel column and paste it to different worksheet
I am trying to extract from an excel file(named as book1.xls) the lowest Price present under the 'Price' column of the sheet1 and want to copy it along with it's respective value which is present in ...
1
vote
0
answers
144
views
establishing a specific graph color based on variables name
I'm looking for a way to set a specific color to a name when creating graphs (line, bar, and pie charts) using excel 2013.
For Example, I want to graph sales results for Anne, Jim, and Mike but want ...
- 11
-1
votes
1
answer
203
views
Populate a cell with data from a sub when excel template opens
Lets say I've created a macro-enabled excel template. In this template I create a sub to obtain a string value and assign it to a variable, say var_1 in the sub. I save the template and close excel.
...
- 125
2
votes
1
answer
116
views
Delete all rows not containing xxxx
I found and modified the below code to remove all rows that don't contain "NHO_Global" in column F, but it deletes everything (it does leave the header row, though).
What did I do wrong and how can I ...
- 21
0
votes
0
answers
222
views
VBA - Assigning Macro to Form Control Button
I wrote the below code in VBA to delete full rows if, in column U of Sheet21 (actually named "ROG Registration") if "Self Cancelled" or "Waitlisted" appear. When I'm on the ROG Registration tab with ...
- 21
0
votes
1
answer
2k
views
Transpose Stacked Data in One Column to Rows based on Variable Criteria and Unique Identifiers
Using Excel 2013 and I'm struggling to write a formula that allows me to transpose the amounts stacked in column E based on the deduction code in column F using various criteria on the first row/...
0
votes
1
answer
2k
views
How to set auto sort on two columns when open the workbook?
I have a workbook that we run for weekly reports, however daily we are adding new records also.
Now, I want to know that how could I set auto sorting on two specific columns every time I open the ...
- 1
1
vote
3
answers
7k
views
Can Excel VBA call Macro in Different Macro Workbook
Can VBA code in an excel spreadsheet call a different XLSM Workbook file's Macro?
I have a local XLSM that needs to call a method in a different XLSM file, how can that be done?
- 21
2
votes
1
answer
1k
views
Dynamic row and column size for Text to Columns VBA code
I export CSV reports which often have around 200-5000 rows or 30-60 columns. Using the record a macro function I recorded a macro that goes through the Text to Columns Wizard, the resulting macro is ...
- 1,156
0
votes
1
answer
3k
views
Auto Hide/Unhide Rows 1 -100 based on value of cell in first column
I have a sheet that I have the first cell A1 given a number of either 0 or 1 based upon input from another sheet depending on a selection from the user.
If the cell A1 changes to 0 then the entire ...
- 11
1
vote
2
answers
861
views
Excel VBA Replace endless case statements with better alternative
I am populating a combobox with a row in the worksheet, then when picking that item in the combobox i want it to hide or unhide that row. It works with my case statements, but how can i do this ...
- 13
1
vote
2
answers
327
views
How identify generic button control in Excel table?
I have created an Excel sheet with a named table. On the left border of the first row I have added a button control labeled "+". This button runs a macro that always adds a new last row on the bottom ...
- 87
0
votes
2
answers
387
views
How to use an Excel User-defined function UDF in Sharepoint's list calculated column?
Thanks to a very good article found on the web, I have build a "TRANSLATE" fomula in Excel (actually VBA code that queries google translate and returns the translated word/sentence in the choosen ...
- 1
1
vote
2
answers
156
views
Excel lookup between dates
I have a spreadsheet that lists each client and the date when their status changed. e.g.
Client_Name Transaction_Date Status
ClientA 1/1/2017 Level_1
ClientB 2/1/2017 ...
- 518
7
votes
2
answers
9k
views
What is Macro worksheet in Excel?
I have Excel 2013. To access VBA I accidentally pressed Ctrl+F11 instead of Alt+F11 and a new worksheet named ‘Macro1’ was created. This looks like an ordinary worksheet but the default column width ...
- 1,698
0
votes
1
answer
105
views
How to match the values of 1 table to another
I have two tables, 1 with a list of list of Students & Subjects, and a second table with subjects as Header & student as Rows.
I need to highlight the corresponding cell where student & ...
1
vote
1
answer
266
views
Can standard excel errors be trapped at input of a custom VBA function?
I would like to know if there’s any way in Excel VBA custom function to recognize that the input data is one of the standard Excel errors e.g. #DIV/0!
Say I have a very simple Excel VBA Custom ...
- 220
0
votes
0
answers
772
views
Excel Crashes and Macro Sometimes work Some time Not
i have created a Excel VBA Macro and a form which filters on a particular column and exported filtering that particular column to PDF.
Form looks like below image:
below is the code:
Private Sub ...
- 133
1
vote
1
answer
2k
views
VBA code to copy certain sheets from closed workbooks saved in a folder to a brand new workbook
I hope that there is a way to solve one of a very time consuming process which I currently perform manually.
I collect input from over 30 people who send to me their excel (xlsx format). So far, I ...
- 11
1
vote
1
answer
477
views
Prevent Function from Being Called Repetitively
I wrote a custom function to concatenate a range, using a custom concatenator.
Public Function mikeConcat(rng As Range, concatenator As Variant) As String
if left(selection.value,1)="'" then ...
- 13.4k
3
votes
3
answers
4k
views
Excel VBA filter String contains Numbers
I have created a Formula in Excel VBA. which Extract String containing Number from a Cell.
for example a string contains : "121A Nariman Street" it will extract "121A"
Below is the Code
Function ...
- 133
0
votes
1
answer
83
views
In Excel, how to create a VBA that returns a data range from another sheet when data validation = to a certain choice?
I'm trying to return data (from A1:D14) on sheet 4 into A2:D:15 on sheet 1 when I select "A" on my data validation list located on Sheet 1 cell A1.
1
vote
1
answer
868
views
Excel 2013: Find a string, replace value in adjacent column, across workbook
I have a workbook with several hundred sheets. For all instances where Clear Votive Cup appears in column B, I need to change the value of column H in the same row (from 2.49 to 1.49). This needs to ...
- 11
0
votes
1
answer
90
views
Setting up data such that the cells with formulas autofill with the rest of my data after pasting
I'm running a macros that pastes data into another work book. The data is updated monthly, and it is pasted into the next blank cell.
The new data depends on formulas in the destination workbook, so ...
- 1
2
votes
2
answers
181
views
Removing Duplicates Across 3K Rows, 9 Columns
I have an Excel 2010 table of 3,800+. I would like to identify duplicates across 6 columns, many with text, some with IP addresses and hostnames, so that accurate data is reflected. I've tried the old ...
- 21
3
votes
2
answers
4k
views
Apply Conditional Formatting VBA to All workbook Sheets
I am very new to this, I am making conditional formatting to highlight numbers above the average of all values in a row.
I need to apply this formatting to all rows in all sheets in the workbook.
...
- 31
1
vote
1
answer
243
views
Dynamically use range function in table; VBA
I have a function which is used to paste a formula into a range of a table.
Is there a way to select the range dynamically. I.e. if the table is resized or a row or col. is inserted?
I.e. if i ...
- 33
0
votes
0
answers
577
views
Sort merged cells with VBA in Excel
Is there a VBA code that can help me to sort the whole column B with the merged cells?
If sort is equal to name1, I only want to see the name1 and all the data within the rows.
|---------------------...
0
votes
0
answers
372
views
Troubleshooting - How to mirror two cells from different sheets in Excel (2013) using VBA
I am working on a VBA setup for an extensive issue tracking file. I have one sheet that contains all of the issues, and is difficult to manage. I have another sheet which is designed to present the ...
0
votes
1
answer
3k
views
Excel 2013 & VBA > Count number of conditional formatting rules on sheet
Excel has a limit of the number of conditional formatting rules a sheet can handle before it locks up and prevents any more from being added. This can become an issue in complex sheets where one or ...
- 554
0
votes
1
answer
3k
views
Excel date to day from sheet name
I'm using this code to grab the sheet name and put it into cell C2.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
I would like cell C1 to display the correct day of the week (ddd) ...
- 55
0
votes
1
answer
849
views
Excel 2013 - removing LegendEntry objects from a PivotChart Legend using VBA
I've got a PivotChart which typically shows 6-8 series out of a possible 15, many of which tend to be zero. So every time it refreshes, it has 15 series and the Legend only needs to list the series ...
- 554
0
votes
0
answers
35
views
Writing a VBA macro that evaluates an IF statement in a specified cell range in SheetA that sets the text color of two cell ranges in SheetB
I have gotten this to run but it is not working properly. (Interestingly, only one cell in my specified SheetB is being changed to the red font upon running.) My assumption is that I am not ...
2
votes
1
answer
31
views
Adding a huge number of lines to a diagram
I have a table with 600+ lines, and wish to add each of these to a chart. All lines have the same x-data, in this case the years (2013-2015).
Fruit Name 2013 2014 2015
Pear ...
- 21
1
vote
1
answer
3k
views
How can I keep history of data changes in one excel workbook
A user posted this in a different question:
Here's some code that will watch the range A1:G12 on whatever sheet has the code in it. If r is the row that was changed, then the code
will copy ...
- 11
0
votes
1
answer
776
views
Using Decimal datatype in Excel Worksheet
I am looking for a way to use the Decimal datatype in an Excel worksheet. I am storing routing information as a number. Routings could consist of 10 moves to 50 different locations. This works fine ...
- 103
1
vote
1
answer
93
views
Macro to check and put the unique value in a cell from column range
I am very new to macros.
I wanted to convert the following formula to macro
=IF(COUNTIF($B$1:$B1,$A2)=1,"NA",$A2)
Where in the $B1 will be incremental and I will be printing the value in $B2.
The ...
- 13
-1
votes
1
answer
45
views
Excel issue - Link a cell to the number of documents in a file
I am trying to get an excel spreadsheet to update when a document is inserted into a file.
For example I have a folder called day diary.
Every few days I will insert a word document or PDF document ...
- 11
0
votes
0
answers
640
views
Excel VBA does not save file even when prompt says it will
I have an Excel file that generates reports using VBA. A portion of that process copies data from 1 file to append into another then saves the destination file. The process works on many machines ...
- 1,105
0
votes
1
answer
1k
views
How do I insert thousands-separators without affecting significant figures in Excel?
I need to figure out how to add thousands separators to large spreadsheets without affecting the amount of significant figures or the dates that are also in the tables. I can't figure out how to use ...
0
votes
1
answer
467
views
Send bulk mail using Excel VBA
Was working on a project which will be sending bulk mail to different people if the conditions are met.
Conditions :
Column U contains the final status (Open or WIP) (will not send if
Closed no ...
1
vote
1
answer
989
views
Show Form from cell click, then CommandButton to pass to next ActiveCell
I'm creating an input form for use with a 'tablet' PC in Excel for our guys to fill out when in the field. As inputs like drop-down menus and the like are quite fiddly, I wish to use a basic input ...
- 11
-1
votes
1
answer
97
views
vlookup - Is it possible to use vlookup to pull specific info like this?
I am trying to pull the following from each group of Numbers -
CHG000509443 05-16-2016 22:48:41 for the Requested
CHG000509443 05-17-2016 13:14:35 for the Approved
Note 1 - It is always the first ...
- 157
-1
votes
1
answer
128
views
Excel 2013 VBA script needed for [closed]
I have several cells that have a string of text that are partially Bold and partially NotBold. I need to delete the Bold portion of the Text in the Cells
Example:
lname1, [email protected]
...
- 13