0
votes
1answer
14 views

Error outputting a string to cell

I am using a vba macro to automate part of an excel spreadsheet. The following line keeps giving me a runtime error 1004# application or object defined error Sheets("Material").Last.Offset(0, ...
0
votes
2answers
23 views

VBA Excel: How to assign .txt file content to a string type variable?

I made a little sub in excel that sends an email according to the parameters you enter: Sub Mail_LSP(mailsubject As String, consignee As String, coalcopy As String, mailbody As String) Dim mlook As ...
0
votes
1answer
13 views

Deleting rows that contain a keyword in the column

I have an excel file that has a id as one of the columns, each id has multiple entries and follow a set of survey questions. I need to delete all of the rows that have that keyword (which is in a ...
0
votes
2answers
37 views

Adding together values from multiple spreadsheets (VBA)

In my Excel workbook, I have three separate spreadsheets. The spreadsheets are identical in format and represent count values for different categories (passenger cars, trucks, etc.). I need to combine ...
0
votes
1answer
24 views

Excel: portable formula to reference data on previous (relative) worksheet

I need to create a fairly simple Excel spreadsheet (workbook). I need 12 sheets (tabs), one for each month. Each sheet references cells in the previous sheet. For example, the "May" sheet is ...
2
votes
1answer
34 views

Replace cell references with values without F9 using macro

This is an Excel macro question. Given a series of formulas such as the following: = A1 + A2 = B2 + B2 etc., ... I would like to replace each of these cell references with the values without ...
0
votes
1answer
35 views

Hide columns based on content (sum of rows)

I'm trying to hide columns based on the sum of selected rows (i.e., cells) in those columns. For example - if the sum of E9:E12 is 0, then hide column E. Additionally, and probably a little harder - ...
0
votes
1answer
9 views

RE: displaying max of direct data input or cell value from formula

With reference to : Excel - how to either have direct data input or cell value from formula I would like to inquire on a related issue. How would i edit my VBA formula if i would like to compare the ...
0
votes
1answer
18 views

Disable or delete right click menus VBA

Whenever I right click on an excel sheet two menus come up: one with cut, copy, paste, etc. and one with font size, bold, italic etc. I can customize the first one to have whatever command I want. ...
0
votes
0answers
33 views

Forfiles SubFolders

I am hoping I can get some help here. I saw a another post similar to my problem and I figured I would give it a try. I have a Forefiles code running in Excel using VBA. The code works great on local ...
0
votes
0answers
24 views

VBA Code to update data in closed excel files within a folder by clicking a button

I have a code which works really well that updates values of closed excel files in a workbook. What I want to do is alter the code so I can create a on my worksheet where I can make y changes I ...
-1
votes
0answers
16 views

Excel or R: Make 1 file for each line of a table, preserving the headers

I have 200 lines in an excel table that I'd like to split into 200 files (csv), with the headers maintained in each file. Is there a way I can do this using VBA in excel, or even R? Many thanks
0
votes
1answer
44 views

Expanding a cell formula resolving all its references (precedents)

In Excel I have a cell containing a formula referencing other cells, which reference other cells and so on... forming a large tree of references. I'd like to substitute recursively all the occurrences ...
0
votes
1answer
19 views

Excel 2007 VBA Export range as jpg file is trucating picture

I have a workbook with many different ranges of data with 77 lines or more. I need to copy them and save as JPG files, which will be used by other application. Below is the example of code that I'm ...
-1
votes
0answers
15 views

Add-in Not working [closed]

I got this code from google and am trying to make it into an add-in. It currently wont work as any time I run it on another xlsm file I get the message. Cannot run the macro "MyGreatMacro". The Macro ...
3
votes
0answers
43 views

show more than 8 items in the Data Validation drop down list in Excel

I have added a simple data validation to a cell but it only displays 8 items in the dropdown. Excluding the combobox solution and probably involving some WinAPI is there a way to make the drop ...
0
votes
1answer
39 views

VBA Lookup by Multiple Criteria - Then Copy & Paste

Currently I have a 2 way database with 10+ fields and 1,000+ rows. Some fields are customer name, car model, year of model, price, date of purchase. I would like to lookup rows of data by multiple ...
1
vote
0answers
31 views

Transferring Data Between Workbooks Based on a Value in a Specific Cell

I have created a form in Excel that moves information entered in the form to the adjacent sheet in the same workbook as well as my agents workbook at a different file path. That piece of the puzzle is ...
0
votes
0answers
25 views

VBA Excel: Macro working well, add-in does half of the job

I created a macro that: 1.takes the value in cell "D11" then 2.trims the value acording to some criteria, then 3.opens another workbook ans searches the trimmed value then 4.copies some info to ...
0
votes
0answers
16 views

Displaying images from database in a pivot table

I've got a table with sales data on MySql server. I can connect to it from Excel and generate a pivot table just fine. One of the fields in the table contains URL to an image of a product. Is there a ...
0
votes
1answer
43 views

Obtaining Frequency of A Number for Each Sub-Heading

I have two columns, one with people and times associated with those people, and one filled with 0s and 1s. For any given person there is some associated combination of 0s and 1s. We can determine ...
0
votes
0answers
31 views

Automation of solver in Excel by VBA macro code

I'm solving a 3rd degree polynomial equation, e.g., -0.003x3 +0.195x2 -1.087x -180 = 0 in Excel. I can do this by SOLVER of Excel and can find the value of x. The problem is the constant term ...
0
votes
1answer
30 views

Convert formulas in table to structural references

I have converted my range in excel 2010 into a table. However, the formulas which were before like that(see below) stayed the same and do not convert into structural references. ...
0
votes
2answers
21 views

Showing name of row instead of excel cell name

I am having extremely long formulas over an extremely big sheet. At the moment I am tracking the formulas with the Formula Auditing Tool. However, my idea would be to just replace for example in a ...
-1
votes
1answer
58 views

Compare the value change of data in Excel between two similar sheets without VBA?

I need to match data in the column A's of two different sheets with similar data and then compare the values in column C. sheet 1 features data from this month, sheet 2 from last month. I have column ...
2
votes
2answers
73 views

How to Programmatically Split Data Using VBA Using Specific Logic

This is an addition to my previous post here. The code that was previously supplied to me worked like a charm, but I am having issues modifying it adding some additional logic. I am creating a macro ...
0
votes
2answers
47 views

Excel concatenation of one column based on values in another

Similar to this: Excel - pivot values in one cell (as comma-separated value) Except it can be a VBA solution, and I do not have duplicates in Column A. Instead, there are blank rows below each value: ...
0
votes
1answer
30 views

Sort with a Macro

I have a spreadsheet with 8 columns of data. (Row 1 contains my headers). I am trying to record a macro that I do not have to edit afterwards. I press record macro, I highlight my data, I go to the ...
-1
votes
1answer
44 views

How to re-arrange Excel database from 1 long row, into 3 short rows of unequal lengths and automatically repeat the process?

This question is an extension/continuation of my previous question at How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process? which was answered by ...
1
vote
1answer
91 views

How do I copy hyperlink only (and not text) to another cell? [duplicate]

I have a spreadsheet where column A displays names. There are a few hundred names and each has a different hyperlink (which links to that person's web page). I want to transfer those hyperlinks across ...
0
votes
1answer
35 views

Set default expand/colapse state on pivot tables

The Setup I have a pivot table in tabular form pulling data from an Analysis Services Cube. I want to calculate the number of days between two dates, but the setup will only allow me to pull in all ...
0
votes
1answer
55 views

How to require a cell input if another cell has a value

I'm trying to edit the VBA for one of my workbooks so that If there is a value in column A, then a value for column C is required or else the file won't save, but i'm having some trouble with this. I ...
-2
votes
1answer
44 views

How to delete specific columns from all excel workbooks in a particular folder

I have a folder in which I have about 30 excel files. In each of these files, I need to delete about 20 specific columns. Here are some details: I am using Excel 2013 The columns are in the first ...
0
votes
1answer
65 views

VBA Solution to VLOOKUP with Hyperlinks

I am looking for some help with a VBA solution for preserving hyperlinks when using VLOOKUP on Excel (2010). I have a load of data on Sheet 1 for internal use only, and a cut-down version of this ...
0
votes
1answer
23 views

Is it possible to change or modify built-in VBA editor in Excel for Mac 2011

Current built-in editor doesn't respond well to my scrolling gestures. I'm having too much trouble writing/finding code. Is it possible to change the editor with something else? Thank you.
1
vote
1answer
63 views

Hide/Unhide rows based on more than one cell value

Please help me I am using the following code to hide rows if cell values are 0: Private Sub Worksheet_Calculate() Dim LastRow As Long, c As Range Application.EnableEvents = False LastRow = ...
0
votes
1answer
67 views

How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process?

I would appreciate help on the above-mentioned topic. I am unfamiliar with Visual Basic for Excel, so will need step-by-step guidance (if solution is via Visual Basic). For example :- Row 1, Sheet A: ...
3
votes
1answer
140 views

Add/Subtract button Excel VBA

Here is an example of my project in this picture I like collecting, so I am making an inventory sheet in Excel. I want to make a way that I can click a button and it affects the next, or previous ...
0
votes
2answers
179 views

Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO". ...
1
vote
3answers
70 views

Trouble using Excel OFFSET function

I have an Excel workbook with numerous sheets entitled "Week1", "Week2", "Week3" ... "Week7". Each week, I input the customers IDs of people buying my product in column C, and -- as I am interested ...
0
votes
1answer
84 views

VBA EXCEL: find method not working when looping through each cell in selection

I'm trying create a macro that, after a range selection, will tell me how many 20ft or 40ft containers where found in the sample. The macro basically looks for the string "1x20" or "1x40" and when ...
0
votes
2answers
89 views

Split single column data into multiple columns based on leap year in Excel 2013

I have about 100 files of MS Excel which consist of weather data. I need the data on yearly basis but the file has a single column with data of about 140 years and manually copy-paste is very time ...
1
vote
1answer
51 views

Can the data validation list width be controlled in Excel 2007 and greater?

In Excel 2007, I can add data validation to a cell, and supply a list of allowed values. However, experimentation suggests that the data validation drop-down list will never display at a smaller ...
0
votes
2answers
76 views

VBA Excel 2011 for Mac: Create custom category for user defined function error

I created several functions i.e.: "cal_adval". I would like to add it to a new function category named "Custom Clearance" I read the documentation, and wrote this macro: Sub add_category() ...
0
votes
1answer
65 views

VBA Code De-bugging in 2010 excel

Looking for some help in debugging some old VBA/Macro code to work in the newer version of MS Excel 2010. The macro in question is the "Material Rollup" function. The purpose of the macro is after ...
1
vote
1answer
28 views

VBA change Script on multiple tables

Via this forum I found a way to refresh autofilters in a table automatically after the data in a excel worksheet has changed, using this VBA script: Private Sub Worksheet_Change(ByVal Target As ...
1
vote
1answer
61 views

Excel - Change cell colour based on a date in the comment or mixed in with text

I am using excel 2010 and I am trying to set up a rule that will change the colour of a cell based on a date within that cell. For example the cell may contain Site meeting - 11.05.14 I would like ...
0
votes
3answers
52 views

Reformat data through macro excel 2010?

I am trying to create a macro that allows me to change the data I received from a client into data I can import into a system. The data I receive is in the format: How Can I do this through VBA ...
0
votes
1answer
46 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
0answers
53 views

Excel calculation uses an unlikely high amount of RAM does not release it [closed]

I have a sheet that contains about 30 matrix formulas, which are calculating with about 5000 rows of data. When I calculate the worksheet manually, RAM usage rises from about 400 MB (idle) to 2.5 ...