Tagged Questions

2
votes
1answer
43 views

How to automate transforming a table into a matrix

I've been looking for a way to automate transforming data in an Excel table into a matrix. For example, from the left table into the matrix on the right, as shown in the following image: Apparently ...
1
vote
1answer
37 views

Select activation method from dropdown instead of writing it yourself

Answer at bottom of post I got the code below to work but a question remains. When I copy and paste the code into my project it won't work. But inserting the outline of the call from the dropdown ...
0
votes
0answers
30 views

Why does my Excel macro fail when run from a hotkey and work when run from the macros ribbon?

I am really confused by what is going on with a macro I created to parse out space delimited files. The macro runs successfully as long as I select the macros ribbon and the macro from the dialog box. ...
0
votes
0answers
16 views

Get rid of the warning box when save an Excel macro-enabled file? [duplicate]

When save such .xlsm file, I got the below snapshot warning which is quite annoying every time I hit Ctrl-S to save. How can I get rid of it?
-1
votes
0answers
36 views

Using VBA to automatically assign gender in excel spreadsheet [closed]

I have to do an analysis on an online membership database within excel, but for some reason, some of the cells under the Gender column remain unfilled. I have found moderate success by downloading an ...
2
votes
1answer
31 views

Excel visual basic function across workbooks?

This seems like it should be a common issue, but I couldn't find anything on it, maybe I'm just searching for the wrong thing. Basically I get sent a lot of workbooks that need specific modifications ...
0
votes
1answer
33 views

Optimally positioning pie chart data labels in Excel with VBA

I have a worksheet where I create a pie chart from data from a database. The chart changes based on the data it is displaying so the slices often change size. I want to display the data labels ...
1
vote
1answer
24 views

LINEST function in excel vba

I am trying to figure out the 2 order equation (Y = ax^2 + bx + c) for a set of data values. When I use the inbuilt function in excel =LINEST(C2533:C2537,B2533:B2537^{1,2}) I get the following ...
1
vote
1answer
28 views

Excel - How to change code to Workbook and not worksheet

I have the following code: Private Sub Worksheet_Change(ByVal Target As Range) Set KeyCells = Range("C9") Set isect = Application.Intersect(KeyCells, Range(Target.Address)) If Not isect ...
0
votes
0answers
48 views

How can I copy/move values below the matching pattern to another column?

I have found this script from this site which moves matching cell values to another column. Sub MoveData() Dim myrange, cell As Range Set myrange = ActiveSheet.Range("B:B", ...
2
votes
2answers
199 views

How to implement MATCH and INDEX functions together in Excel VBA?

I am trying to find the row number of the first cell that is less than zero. So, by browsing through the net I was able to come up with this formula which works perfectly fine: ...
-2
votes
1answer
51 views

macro problems with loop [closed]

if I have in cell "A1" any value like " my name " i have to separate tis value to many cells to be shown at cell "A2" (M) , cell"A3" (y), etc. . after that i have to look up for every cell value from ...
0
votes
3answers
65 views

How to update cell references when setting the Formula property in VBA?

Here is a snippet of my code: For i = 1 To k If Range("D2").Offset(i, 0).Interior.ColorIndex > 0 Then Else Range("D2").Offset(i, 0).Formula = Range("D2").Formula The ...
1
vote
2answers
116 views

Passing Range to ABS function in VBA

I would like to perform a function which looks like this Selection.FormulaArray = "=GEOMEAN(ABS((C646:E646)))" Since my range keeps varying I would like to pass the range as a variable which looks ...
0
votes
2answers
68 views

Can't save Excel VBA as xla

I have my VBA code ready to have others test, and i was going to give it to them as an Add-in (.xla). I had tested this with some other code, to make sure i could do it, and was able to create an .xla ...
0
votes
1answer
67 views

creating multiple plot per level of factor (trillis graph) in excel

Is there way to automate the process to make graphs like the following (also known as trallis graph) in excel ? I know there are some R interface with Excel (some of them need to be purchased), but I ...
0
votes
2answers
97 views

How can I turn a Calculated Column back into a data column in an Excel Table?

I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new ...
1
vote
1answer
64 views

How to inspect / view an Excel macro before “enabling macros” for a sheet?

I'm receiving occasionally an xls file that contains a macro which I should use. If I don't have complete trust in the sender, I wish to look at the macro's source in order to verify that it indeed ...
0
votes
1answer
82 views

Cutting and Pasting in Excel Macro

I have approximately 9,000 columns (goes to column MGN) which have 14 rows of data points. I need to get the data in a single column with the format Column B below A, C below B, etc. Is there any ...
0
votes
0answers
37 views

Unmerging vertically in excel using VBA

I have an excel sheet which contains merged data cells. multiple classification is being done using the merged cells. I want to unmerge tham and dump the raw data into anotehr sheet. this is my task. ...
3
votes
2answers
251 views

Excel - concatenating cells where reference number matches [duplicate]

I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...
0
votes
1answer
79 views

Nested for each loop 10 by 10 gives over 1000 loops

I'm seriously dumbfound by this issue, I have a nested for-next loop that gives over a thousand loops I know this from seeing this code: 'find row ranges for department 1-10 For i = 1 To 10 Step ...
0
votes
2answers
220 views

Automatically import a macro to Excel and run it

I have made a module i.e. a .bas file which does the work I want to do in Excel. I have to work with this same module in four different files everyday. Now I want, let's say, a script which ...
0
votes
1answer
118 views

IE8: Pressing 'Open' in Download Dialog saves file to a temporary, non-writable location

I have a specific problem with IE8. Our users download excel files via IE8. Those excel files contain macros which create pivot tables. In order to do that, excel 2007, needs the file to be writable ...
0
votes
1answer
302 views

Excel: How to hide/remove scrollbars from Pivot Table Slicers?

Slicers are a fantastic tool new to Excel from 2010, allowing you to quickly switch your selections when analysing data using a pivot table. Sometimes there are too many options to fit in the slicer ...
0
votes
0answers
168 views

Excel VBA macro to sort sheets in a custom order?

I have a workbook with over 20 sheets. Each sheet contains an error type, and a list of records with that error. These workbooks are produced elsewhere and then sent to me. I want to sort the sheets ...
0
votes
3answers
84 views

Looking up (text)values in Excel array

I was searching around on internet and playing around in Excel 2010, but could'nt really find an answer. For the sake of cleanliness I made an abstract, but the real file is a lot bigger. In ...
0
votes
2answers
598 views

Excel 2010 Macro - Copy cell range with formulas and paste values only

I want to create a simple macro that will copy a range of cells with information generated by formulas on sheet 2, to a range of cells on sheet one. I need to paste only VALUES on page 1, leaving the ...
2
votes
1answer
71 views

Is there any way to programmatically stylize part of a string in a calculated cell in Excel 2010?

Let me explain what I mean. I have a cell that contains something like this: ="String of text that's moderately long" & YEAR(NOW()) & "." And I want to know if there's any way that, via ...
0
votes
1answer
114 views

MS Excel data validator does not accept call to the custom macro

I have the working VB macro that validates the content. It takes two string parameters (cell value and regular expression) and returns either True or False. It uses CreateObject("VBscript.regexp") ...
1
vote
2answers
489 views

Combine duplicates in Excel, sum some columns and weighted average the others

I have a large excel spreadsheet with duplicated rows, I need to condense the duplicates into one line with some of the columns summed and the others weighted averaged. Is that possible? It is a list ...
0
votes
1answer
88 views

How to achieve something like this in Excel?

So I have a table full of data which looks like this: Subject A Subject B Subject C Subject D Count Person A F F F 3 Person B F ...
0
votes
0answers
160 views

How to fix corrupt VBA Code

I have a large (2MB) .xlsm format file with a lot of macros. I put this file on a network-share drive (not cloud platform). If I overwrite it, or Save As with a new name, the new version is fine on my ...
2
votes
2answers
338 views

VBA to save document in Excel and Word periodically (AutoBackup)

I want to create a VBA script that would save a copy of the document every 5-10 minutes with a new name. The script would keep like 10-15 copies in the same folder, incrementing a number each time. If ...
2
votes
1answer
145 views

Can I get VBA error messages in English language?

I'm using MS Excel in the Dutch language. When I get error messages they are also in Dutch. Is there any way to get these messages in the English language? edit: It's Excel 2010 (MS Office ...
1
vote
1answer
146 views

Chart corrupt after .SetElement 'The specified dimension is not valid for the current chart type.'

I have an Excel 2007 workbook with multiple Charts on one worksheet (stacked bar chart and one series as (invisible) line for the totals). Depending on a cell selected by the user, the bar charts ...
0
votes
0answers
152 views

VBA Excel Script for Following Hyperlinks

I have a workbook that contains over 40 worksheets. One worksheet is the main worksheet, or directory, and contains hyperlinks that refer to the other worksheets. The code makes it so all the other ...
1
vote
0answers
36 views

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

Possible Duplicate: 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 ...
2
votes
3answers
2k 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 ...
0
votes
3answers
339 views

Any way to style the linked Excel Table in PowerPoint?

I have a linked table in my PPT (PowerPoint 2010), which is from a portion of an Excel 2010 worksheet. When I update the data in Excel, the table updates automatically in PPT, which works fine and as ...
2
votes
2answers
104 views

Auto Updating an Excel Filter

Firstly I'm using excel 2010. I have had a look at other "auto-update" and from what I saw they wanted you to type some information in to make it "refresh" the auto-update. Essentially what I have ...
0
votes
0answers
88 views

Creating a Macro or Formula to copy data through a query

Here is my formula that I'm looking to build off of: =IFERROR(VLOOKUP($A1,Table2,10,FALSE),"") I don't know if I can use just a formula or if it has to be a macro. I am trying to create a query ...
1
vote
2answers
252 views

How to replace cell formula with it's own value?

Problem Description I have a custom formula that I wrote, but it takes a while to calculate the answer, and since excel updates function values (When you activate the workbook, or re-open the ...
1
vote
1answer
59 views

Removing rows with a column mismatch compared against a list

I spent some time googling this and couldn't find anything that I could modify to solve my problem. I'm starting with a sheet consisting of 7 columns. In column C there is a five character ...
0
votes
2answers
134 views

In Excel, how can I clear the contents of a cell referenced in another cell?

I am trying to clear the contents of a cell based on an indirect reference. For example, cell A1 contains the address to another cell (i.e. B2). Any idea how to clear the contents of the cell ...
0
votes
2answers
474 views

Change the pie chart colors

Can someone provide an example of how to change the colors of a pie chart? (The actual pie chart pieces). The pieces are referred to as Points? I'd like to choose a color using RGB for each piece. ...
1
vote
1answer
166 views

Format columns matching drop down value Excel 2003/VBA

I have a drop down in Excel to allow user to select a comparison bank. When a bank is selected for comparison all occurrences of that bank are highlighted in the tables below the drop down. Here is ...
1
vote
1answer
289 views

Excel Macro pattern matching filter

Can anyone help me write this as a VB/excel macro. I would like to select the following from one data sheet and write to another data sheet. Select any rows where in column T the field contains the ...
0
votes
0answers
55 views

How to read the caption of a button in an Excel ribbon programmatically in VBA?

I have a third party ribbon installed in Excel 2010. I want to be able to read the caption of the buttons in the ribbon. The caption, which can be either 'Pause' or 'Resume', indicates the state of ...
-1
votes
1answer
96 views

Disable abilities to “create shortcut” and “send to desktop” through Excel?

Is there any macro solution to eliminate the "Create Shortcut" and "Send to Desktop" options from right click? If not, is there any way other than messing with user permissions? Thank you so much!

1 2 3 4 5