I'm trying to create a macro to insert a formula into a specific cell. The formula is meant to check if a cell has text, and then if it does, search for the text on another page.
I had a go at the code, but keep getting Runtime error 13.
I'm using Excel 2010
Code:
Sub new_entry()
'
' NEW_ENTRY Macro
Dim rowNo As Integer
[Code]....
I am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.
Option Explicit
Sub alex_it_saving_lives()
'get the image folder path
Dim strImagePath As String
strImagePath = GetImageFolderPath
If (Len(strImagePath) = 0) Then
[Code] .........
using excel 2010, I would like a macro that will insert a page break every time a row is blank. There are two blank rows between each data row which I need to be separated.
View 9 Replies View RelatedUsing Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
Just recently My Excel 2010 has decided to not let me right click to format cells, delete or insert rows. I can do these functions from the ribbon, but not via right click. this happens in both existing spreadsheets where I am the author, or even a brand new spreadsheet like in the image below. we have restarted the computer, Uninstall and reinstalled Office and still get same symptoms. I got here thru google but cannot find an answer anywhere.
I am very computer literate and even our IT personnel have looked at this with no answer. as you can see in the image, these options are greyed out.
Running Office standard 2010, Windows 7 pro x64
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
I'd like to apply multiple formula to a set of cells on a summary page. My summary page also contains 3 variable dropdowns, and I'd like to display data based on the text selected in those dropdowns (pulling data from 2nd tab "Variables")
The following formula works in the first instance:
=IF(AND(H4="Product Type A1", H6="External", H8="Existing"), Variables!C4, 0)
What I'm struggling to do is add additional formula to the same cell in order to deal with the remaining eventualities of the drop down variables:
Variable 1:
Product Type A1
Product Type A2
Product Type B1
Product Type B2
Variable 2:
Internal
External
Variable 3:
Existing
New
Or am I better using a VLOOKUP or something?
I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.
The formula I have in mind would be something like
=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......
I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.
The situation is that I have 3 interest income from 3 different companies. Column B acts as a helper column to determine if a partner receives just one interest income or receives multiple interest income. If they receive multiple interest income, I want column B to display a 1 and if not make it blank. If you notice partners 5, 9, and 16 contain two or more interest incomes (Columns C - E). All the cells are have either an amount (positive or negative) or a zero. There are no blank cells. Columns (F - H) contains the interest income amount if only that partner receives just one interest income. So for those partners they should not display any of their amounts. The reason is because I am using a word template and currently have to use multiple templates ( 3 in total) to display correctly for each partner. I got this to work with a word macro that will collapse all non blank columns in a particular table. This might be on a ongoing project for me as I do have more logic to solve.
Excel 2010
A
B
C
[Code]....
The concept is to have a user open a sheet that breaks down that current day into 15 minute blocks of time. Later, I will work to append that to an Access DB or to a master Excel sheet as I will have multiple engineers inputing times for multiple days. There are multiple paths for this....We don't have Access for everyone yet or I would look at that path now.
Right now I am trying to make the initial input screen easy to use so that they actually use it.
I want to allow the user to select multiple 15 minute blocks and then click a button that adds a specific value to each of those cells. For instance, they might choose 8 15 min blocks that are not necessarily blocked together (C5, C7:C10, C15, C20:C22) They would hit the "Project 1" button and "Project 1" would be inserted into each of those cells.
I have about (5) categories so I would simply have (5) buttons with the different inptu strings.
But how can I have a macro set the value for multiple selected cells? Ideally, they would not have to be blocked together but, if there is no other way, if I could have blocks of cells filled in all at once.
I am using Excel 2010.
only a few of us have Access but I will be the only one accessing the collected data. Besides, I would make Excel query the Access DB for more general use. I
I have two spreadsheets in Excel 2003.
Spreadsheet 1 has 10 columns of data (A-J). I want to copy a variable number of rows from spreadsheet 1 to spreadsheet 2.
When I paste into spreadsheet 2, I'd like to automatically insert blank cells in three places, taking the total number of columns to 13. I'd like columns C, F and I to be blank, and the last column with data to be M.
I will perform this task regularly, and add the copied cells to the bottom of spreadsheet 2, so I'd only like to insert blank cells within the range that I'm copying, not the entire spreadsheet.
I will then populate the blank cells with a VLOOKUP function. Do I need another macro to automatically add the formula to the cells, or is there a way to include this in the cell-inserting macro?
I need to copy two adjacent cells down every 7 rows in the same column.
Would do it manually but have some 4000 rows.
I need Macro to Format all cells in column according to value. The cutoffs are >20, between 21 and 50 (>21;<50) and greater than 51 (<51).
I have macro to consolidate cells from multiple files to single workbook. Now I have to 'color code' the value in all cells in column 'B', all as part of one large macro.
In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):
VB:
Dim vListEnd As Variant
Dim vTaskCount As Double
vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
vTaskCount = ((vListEnd - 5) / 22)
My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.
Within each reporting segment, there are two different formulas needed.
For the first reporting segment located in rows A5 thru J27:
I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).
Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.
My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)
I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:
All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.
Date
All Test
Test1
Test2
Test3
Test4
$ Amt.
Jan.16
=SUM(C2+D2+E2+F2)
[Code] ........
The first four sheets look like this, which is why they work fine:
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
[Code] ........
The fifth sheet looks like this: I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.
Date
All Test
Test1
Test2
Test3
$ Amt.
Jan. 16
=SUM(C2:E2)
=SUM(C3:C5)
[Code] ......
Is there any way in Excel 2010 to get it do what I want? (I am not sure how that one cell became outlined and I do not know how to fix it.)
I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.
The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.
I am using the following ....
I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.
What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.
Using: Excel 2010
Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell
[Code] ...........
I am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.
Below is an example of the contents of six cells. Some have just text, but some have text and dates.
1st Time Attender: 4/8/20121st Time Attender:1st Time Attender: 4/5/20122nd Time Attender:2nd Time Attender: 4/8/20122nd Time Attender: 4/5/2012
In this example, I would want the new cell contents to be:
4/8/2012
4/5/2012
4/8/2012
4/5/2012
how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:
For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND($E$8>=M2,(($E$8-$D$8)>=(N2-$M$2))), color index is 40
condition 3: Formula Is =AND($F$8<=M2,$G$8>=M2), color index is 39
I want to add:
condition 4: Formula Is =AND($H$8<=M2,$I$8>=M2), color index is 40
condition 5: Formula Is =AND($J$8<=M2,$K$8>=M2), color index is 39
and so on
The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.
resource tracking ex.jpg
I'm keen to add a field to a spreadsheet that populates a cell with the username of the last person who edited the file, as in the system or logon name, e.g. first.lastname syntax.
I would however like to keep this sheet as an .xlsx and not have it as an .xlsm. I have the code to use VBA, but is there way to grab the username without VBA to keep the .xlsx format?
I'm using Excel 2010.
I am using Windows 7 and Excel 2010.
Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?
I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)
Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......
Here is the table:
I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green
LEGEND
Earned Value Limits
Milestone
RED
Yellow
Green
Turquoise
Blue
M2-M3
2.15
M4-M6
1.66
M7-M11
1.26
I put the "Insert new equation" button on my "Quick Access Toolbar" but it is grayed-out, as in, unavailable. The weird thing is, I also have a "Insert Equation" button on the same toolbar, clicking that opens a small menu of ready stored equations and at the bottom there is another "Insert new equation" button, that is also grayed out. However simply clicking the "Insert Equation" icon puts allows me to insert a new equation, as it should be. The same is true for the "Insert Equation" button in the "Insert" tab.
I am working on a new excel file, i.e. it is a .xlsx file with only one sheet. It worked for me before but I did a windows reinstall and now it doesn't.
Also while on that subject in Word 2010 you can put a "Insert Symbol" button on the "Quick Access Toolbar" which, when clicked, opens a small menu of recently used symbols, while its Excel counterpart directly opens the entire Symbol catalog. Any way to put a button on the Excel "Quick Access Toolbar" that behaves like its Word 2010 sibling?
I've recently purchased an add-in for Excel 2010 called Image Assistant. It allows you to insert loads of images at once and automatically puts them in the cells for you, which is what I need to create quotes for my business. It was painstaking to go through resizing each picture individually so I thought I'd give this a go...
The demo of the program worked fine, except it restricted you to one picture at a time and I have thousands to go through. It resized the picture great and put it in the cell as I expected.
So I purchased the full product. There didn't seem to be an option to just put a license key into the demo version to make it full, and I was sent a link to download the full version, so I did.
When in Excel, I click the icon in the add-ins window and Excel grinds to a halt. It stops responding instantly and then won't do anything until I force close it, at which point it pops up with "Automation Error" and then closes the program.
I've checked DEP settings, trust center settings, add-in settings. I've tried 'repairing' Microsoft Office in case it was an issue with Excel itself but that didn't work. I've uninstalled and reinstalled the add-in several times and it still freezes every time I open it.
Problem signature:
Problem Event Name:AppHangB1
Application Name:EXCEL.EXE
Application Version:14.0.7109.5000
Application Timestamp:522a4031
Hang Signature:31f9
[Code] ............
I'm using a macro to insert a photo in to a Excel 2010 workbook. If the photo moves location it will no longer display the photo in the document as I belive the photo is being referenced instead of embedded.
If you then use the "Change Picture" icon the photo embeds but how do I get my macro to embed the photo.
Sub InsertPhoto(PictNo)
Dim SH As Worksheet
Dim Rng As Range
Dim MyPic As Picture
Dim sPath As String
Dim PicH As Single
[Code] ......
I have an Excel spreadsheet that where the headings start in row 5. Data starts in row 6.
The spreadsheet is updated frequently. The headings are always the same but the number of lines of data changes.
D5 = Units1 (field contains numbers or is blank)
E5 = Units2 (Field contains numbers or is blank)
I would like to insert a column to the right of Column E and add D + E in the inserted cell. I would like F5 to be called NewUnits.
Some cells in Columns D and E are blank. No cell in Column A is blank. (If cell A6 is the activecell and you press CTRL + Down Arrow Key -- you will get to the last cell with data in Col A. That is not the case if the activecell was E6 as there are blanks.)
I am using Excel 2010.
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else
Row 2 is my header, so cell A2 says "ID"
Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
I can use the MATCH function when i am in excel but i am having trouble getting it to work when i am trying to insert the formula using VBA.
Here is the code i have but it wont even compile........
I am using excel 2010, i try to insert active x control command button insert but " cannot object insert" like this message came?
View 3 Replies View RelatedI am looking to insert a drop down box in Excel 2010 which will allow users to select multiple items. At the moment I have data validation drop down lists in other columns, however I need one column to allow for multiple selections.
Basically I have a drop down which allows for a region to be selected, my next drop down (in the next column) I need to allow users to either select "ALL", or multiple countries which fall within the region selected previously. I need a check box so that users can select multiple countries, and deselect if an incorrect country is selected in error.