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] .........
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]....
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?
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 am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
View 12 Replies View RelatedIn Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies View RelatedI am using Excel 2010 on Windows 7.
I copied (with 'Paste Link') rows from a master Excel file to a file that performs calculations. This file (and many others like it) need to be modifed by other people. My tester has informed me that the data is not updating between the files for her, although it does work for me.
When I check 'Data->Edit Links' it shows the status of the file as 'unknown'. If I click 'Check status' it is then OK, but the next time I look at it, it has reverted to 'unknown'.
1. How do I get the status to remain at OK?
2. Why do the links work for me, but not for my end users?
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
[Code] .....
Untitled.png
I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
View 5 Replies View RelatedI have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies View RelatedIn an Excel Pro Plus 2010 workbook on a Windows 7 PC, trying to use the "After" argument in a VBA .Find function and it's not working as expected.
I've got the following data in column D:
Row1: Title
Row2: Row2Data
Row3: Row3Data
And using this simple procedure:
Sub FindTest()
If Range("D:D").Find("Title", After:=Range("D1")) Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print Range("D:D").Find("Title", After:=Range("D1")).Row
End If
End Sub
My expecation is that "Not Found"will be returned, but instead I'm getting the row # (1).
If I change the value in D1 the procudure returns "Not Found", as expected.
If I change the find argument to look for "Row2Data" it returns 2.
I also tried changing the "After" argument to "D2" ... same result.
Finally, I tried using this format: "Range("D:D").Find("Title", [D2])" and got the same result.
In excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
I have a long bit of code that at one point saves a Word document and then saves it again with a new name and the old (legacy) .doc extension.
This all works find when the user is running Office 2007. However, it errors out for using Office 2010. The reference libraries are all correct (as far as I know).
Code:
Dim myDoc As Word.Document
Dim saveAsName as String
saveAsName = "some text here" + ".doc"
myDoc.Save
myDoc.SaveAs fileName:=(saveAsName), FileFormat:=wdFormatDocument
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
I'm working on a macro that does the following:
1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values
- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:
I'm running Windows 7, Office 2010
Code:
'Chart variables
Sheets("Summary").Select
Dim ChartRange As Range
[Code]....
I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)
The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.
When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line
Error Message: Unable to set the Visable Property of the Pivot Item Class
Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#
[Code]....
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
I am trying to hide the excel 2010 application, open a csv file on a network drive, show the excel application.
The problem is a "Downloading" progress indicator appears, during the workbook open event I assume.
Code:
Sub Macro1 ()
Application.Visible = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="S:LOGT5.CSV"
Application.Visible = True
Application.DisplayAlerts = True
I have seen the similar progress indicator during the "Save" event too. How to hide this?
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 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.
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 trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
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.