Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [vba]

Visual Basic for Applications; a subset of Visual Basic used in all Microsoft Office applications. Consider using this tag together with the tag for the specific application in question. Good questions on VBA generally contain some code and asks for solution of specific issues. Questions asking for entire solutions (please give me the code / how to do this and this with VBA) are off topic and will be closed as too broad.

19
votes
2answers
9k views

How do I add VBA in MS Office?

I've seen posts showing answers which use VBA (Visual Basic for Applications) code/macros, however, I've also noted additional comments afterwards about how to use the VBA (how to implement and ...
10
votes
5answers
174k views

How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this: ...
5
votes
2answers
19k views

How do I use Excel 2010 Personal Macro Workbook?

I want all of the macros that I've written to always be available when I'm using Microsoft Excel 2010. How can I do this?
2
votes
3answers
11k views

How do I transpose data in columns to rows?

How do I transpose data in columns to rows? If the data is: Joe some city some state Bob no city no state and I use Paste Special » Transpose, I get Joe some city some state Bob no ...
7
votes
1answer
12k views

How to replace text in the body of an incoming message with a hyperlink in Outlook 2010?

Does anyone know of a way or a program that will read a received email and recognize a static format of text (in my case it will be "#" followed by 6 integers), and make that a hyperlink to a webpage? ...
4
votes
1answer
2k views

Excel: Dual graded color scales

Is it possible to do conditional formating for two graded color scales over the same cells? For example I wish to conditionally format a cell such that its color is: Green to Blue graded scale for ...
6
votes
1answer
62k views

Inserting a (jpg) picture into a cell in Microsoft Excel 2007

Is it possible to insert a jpg image inside a cell, or do Excel cells only display alphanumeric characters (or references)?
5
votes
1answer
46k views

Find and replace multiple values within selected cells

I want to run a find and replace on multiple values within a range in excel with values from 2 columns: A with the original word; B with the translation. I already found the VBA code to get this ...
2
votes
1answer
1k views

Excel VBA code Private Sub Worksheet Change Power function not working

I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is ...
4
votes
3answers
4k views

Calculating the average of variably sized blocks of numbers within a column?

In column A I have a set of numbers (over 1,000), and I want to get an average of a block of them at a time, (e.g., 10, so A1:A10 then A11:A20 and so on) and write these averages into column B, such ...
1
vote
3answers
709 views

Strange residue on cell with Data Validation when using Excel VBA

Thanks to Raystafarian I get rid of that left over Data Validation residue box by deleting all of them from the work book before exiting. Private Sub Workbook_BeforeClose(Cancel As Boolean) ...
14
votes
1answer
35k views

Recover sheet protection password in Excel

I have an Excel workbook. Two years before I set password protection option for one of the sheets. Now I have forgotten the password. Is there any way to recover the password of that Excel sheet?
2
votes
1answer
76k views

Execute SQL Query with VBA and fill sheet with data

Very new to any form of code. Attempting to run a script in VBA that will pull data from an access database using SQL and put it into an excel spreadsheet. Code is similar to below. Runs clean but I ...
1
vote
1answer
3k views

Create timestamp formula for Excel

The idea is simple, I'd like a function I could do something like =MOD_DATE_OF(A1:A4) and when any of the cells in such range is modified, the cell I assigned that formula gets the current date. I ...
1
vote
1answer
5k views

How do you prevent corruption of shared Excel files?

I have 10 or so Shared Macro Enabled Excel files that about 30-50 users modify multiple times each day. Over time the files get bogged down and bloated from excel thinking users are still using them ...
2
votes
4answers
48k views

Filling cells with sequential numbers in an Excel (2003) macro

I need to fill an excel column with a sequential series, in this case from -500 to 1000. I've got a macro to do it, but it takes a lot of lines for something that seems like it should be a single ...
1
vote
1answer
565 views

Sorting cells in a column is easy. But how to sort cells in a row?

Assume school language learning exercise, based on making right order of words in a scrambled sentence. Then put numbers into the next row, specifying the order the words should be in. Now select the ...
0
votes
1answer
3k views

How to combine values from multiple rows into a single row using module?

I'm looking to combine multiple rows into a single row based on a value in the code column.. I've noticed other questions on here similiar to my question but I cannot seem to extend the range.. Code ...
15
votes
2answers
70k views

Any Excel function that will reverse a string?

Are there any built-in Excel functions that will reverse a string?
5
votes
2answers
1k views

how to remove or change background fills in all tables in a microsoft word document

I have a bunch of large documents, with many tables that have different and inconsistent fill styles - some of them are not readable in black and white when printed. I cannot change the background ...
7
votes
8answers
144k views

Excel VBA “Unselect” wanted

I would like the final appearance after my VBA has finished running to be empty of selection -- to have no cell or range on any sheet colored (if it was range selected) or in a bold line box (anything ...
6
votes
3answers
23k views

How to add custom styles to navigation pane or change them with VBA

I have a Word2010 document that was created by a tool from Axure. Each heading is formated with a custom style ("AxureHeading1", "AxureHeading2", "AxureHeading3"). I was able to create a table of ...
5
votes
3answers
19k views

Extract data from delinked Excel plot

I have a Word file which has some Excel plots in it. Unfortunately I lost the original excel plots and the word file is now 'de-linked'. Is there a way to retrieve the lost data for the plots? Just ...
2
votes
2answers
8k views

How can I compare two columns in Excel to highlight words that don't match?

(I'm using Microsoft excel 2010) Lts say I have a list of phrases in both column A and column B (see screen shot below) What I would like to happen whether it be with a macro, VBA or formula is: If ...
2
votes
3answers
144k views

VBA code to hide or unhide rows based on a cell value

Heres my code, but its not really doing anything, I dont see anything wrong with it: Private Sub PG1(ByVal Target As Range) If .Range("E50").Value = "Passed" Then Rows("51").EntireRow....
1
vote
1answer
1k views

When using multiple accounts, how to ensure emails are sent from the correct account

I use 2 email accounts with Outlook. One for work, one for personal. The problem I have is I am forever sending work emails from my personal account! How do I set up a list of "do not send email to ...
1
vote
1answer
3k views

Filtering by currency in Excel

I have this situation in Microsoft Excel: Cells are formatted by using "Format Cells" and setting the Currency manually for each cell. So, they are not simple strings. I would to sum filtering by ...
4
votes
6answers
42k views

Excel ran out of resources while attempting to calculate one or more formulas

I received the error message "Excel ran out of resources while attempting to calculate one or more formulas" on ONE of my computers (Out of 2). My worksheet contains: (Inside a single worksheet, not ...
3
votes
1answer
12k views

Excel Large Function with Duplicates

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...
3
votes
1answer
2k views

Converting All Plain Text Links to Hyperlinks

I have a rather large Word file (+1,100 pages) that contains plain text links of media coverage. For instance, the Word document contains: The News Leader (AP) Va. police officer who died on her ...
2
votes
1answer
10k views

Copying data from multiple word docs into one excel sheet

I have about 4000 doc and docx files which contain a table. I've managed to import these into one excel sheet using the following script: Sub Macro1() Dim xl As Object Set xl = CreateObject("...
2
votes
2answers
7k views

VBA Excel 2007 help to speed up code to hide rows [closed]

I am trying to write a VBA code that automatically hides rows if a value in the row is equal to "". My range of values is A37:A400. Because my range is so big my previous VbA is to slow and looks one ...
1
vote
2answers
1k views

Transform multiple columns in Excel table into multiple rows

I have an excel table in the following format. ID Date1 Date2 Date3 1 1/1 1/2 1/3 2 1/2 1/3 1/1 3 1/3 1/2 1/4 Is there a way to change it into this format? ID Date 1 1/1 1 1/2 1 ...
1
vote
1answer
534 views

How to create multiple rows combining data sets

I have three tables and i need an out put which has data from two tables, i would like to create the same using macro. Table A A B C D Table B Apple Orange Pears Table C Americas Asia Europe ...
0
votes
1answer
7k views

excel vba: copy rows if data match with values in column in another sheet

I have ask a related question here. Sir Adelaide has provided me this very useful solution. So now, in this almost similar case, I have 2 excel sheets in my workbook. [Xsheet][1] Sheet1 I'm gonna ...
0
votes
2answers
124 views

Microsoft Excel Macro for seperate rows to four different columns [closed]

I have an excel sheet that contains names (1st row), job titles (2nd row), company/institution (3rd row), and addresses(4th row) in one big column. I am trying to separate them into four different ...
0
votes
1answer
901 views

Clever way to connect multiple images to on-click macro - VAB Excel

I have asked this question before, but admittedly in a terrible way, so I am asking this again. So here I am once more. I am making a program in VBA Excel - or better, combining a certain worksheet ...
0
votes
4answers
8k views

How can I tell if sheet a exists in a workbook?

I have the following code that's not working as expected: If Sheets("a") <> "" Then MsgBox ("sheet a exists") How can I tell if sheet a exists in the workbook?
0
votes
0answers
200 views

Worksheet_Change to Worksheet_Calculate

I need help re-writing my original code question to a worksheet_calculate. I am new and trying hard to learn but am stuck on this one. When I use Calculate in place of the change, the code throws ...
-2
votes
2answers
509 views

Excel VBA code to turn a row with a variable number of columns into fixed-length rows

I would like to change this: Filer ShareName IPAddress1 IPpaddres2 IPAddress2 ... ... .... ServerA /share1/tools 192.168.1.52 192.168.1.65 192.168.1.158 ServerA /share/library ...
27
votes
9answers
34k views

How can I use custom colours in the Microsoft VBA editor?

I'm working on a few projects for work using Excel and VBA, so I have no choice but to use the Microsoft Visual Basic editor. I normally wouldn't mind, but I can't figure out how to set a custom ...
35
votes
17answers
225k views

Copy Excel worksheet and maintain relative cell reference in formulas

Another copy problem in Excel: How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[...
6
votes
3answers
43k views

How can I find out which parts of an Excel workbook are the biggest in size?

I have an Excel .xlsx that weights 47MB. I'd like to know which parts of the workbook are the heaviest and work them, since I haven't made much progress manually trying to identify and reduce the ...
4
votes
2answers
295 views

VBA Syntactic Sugar

I use VBA in my work every day, but I didn't know about Range syntactic sugar. I just knew last month when I saw MSDN Range page. I tried to find an article with this features, but I didn’t find any. ...
2
votes
1answer
2k views

How do you unprotect the VBA macros for a macro-enabled workbook if you forgot the password?

I have heard myths about how easy it is to crack a VBA macro password, but haven't been able to figure out how to do it myself. We have an excel file at work where the VBA is password protected and ...
10
votes
6answers
205k views

Adding an element to the end of an array

I would like to add a value to the end of a VBA array. How can I do this? I was not able to find a simple example online. Here's some pseudocode showing what I would like to be able to do. Public ...
6
votes
2answers
7k views

What would an Outlook 2007 macro to automate Paste Special - Unformatted Text look like?

I'd like to assign a macro in Outlook 2007 to a Toolbar button that would execute the equivalent of these clicks when there is formatted text in the Windows clipboard: Click the Paste icon by the ...
5
votes
3answers
92k views

Trigger macro with ONE click on specific cell

Would you mind pointing a way to start a macro in Excel 2010 by clicking only once on a specified cell? I had seen a solution somewhere, but now I can't trace it back.
4
votes
1answer
25k views

Show Excel column filter information in cells

We have a sheet with a huge number of columns and filtering is often used to navigate to the correct data. The problem is that sometimes its not obvious that the filter has been applied , the visual ...
4
votes
1answer
7k views

How to run a macro on a shared mailbox in Outlook 2013

I am working with my own mailbox, but also with a shared mailbox. I have a macro to automatically save attachments in e-mails which I receive, but this works only for my own mailbox and not for the ...