472,958 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,958 software developers and data experts.

How to create a Macro that runs on a selection of cells

OK, So I have a very specific problem that I hope to make not-a-problem, but I need some help. I am a complete neophyte when it comes to making Macros.

Iíve got a customer that has put long lists of references in a single column in Excel (normally, the references are just in one cell). Iíve figured out how to consolidate a selected portion of a column of references into a single cell (see my procedure below), but what Iíd really like to do is to turn this into a Macro that does it instantly.

The problem is that Iíd like to be able to run a Macro on a selection, that is, Iíd like to select the cells and have the macro run on ONLY those cells. Then I could select the cells, run the macro, make another selection, run the macro, etc. This would speed up my work tremendously.

My problem: I canít figure out how to make the Macro run on just a selection. The Macros Iíve recorded only run on the selections Iíve made while recording the Macro. Is there any way to make the Macro generic, so I can select cells, and then run the Macro on them?

Combining References in Excel

1. Letís say the references you want to combine are in E18:E27
2. Make a new column next to the reference designator column, for consolidated references
3. In a blank cell in the new column, where you want to combine all the values, type:
5. Then select the cells that need to be combined
6. The formula will change to =CONCATENATE(TRANSPOSE(E18:E27
7. Donít press enter yet.
8. Click after the last cell reference and hit spacebar
9. Type the & operator
10. Type Ē, Ē (double quote, comma, space, double quote)
11. This will add a comma and a space after every reference
12. Select TRANSPOSE(E18:E27 &", " and press the F9 key (F9 replaces formulas with values)
13. This replaces TRANSPOSE(E18:E27 &" " with its result, {"E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "}
14. Now remove the curly brackets { and }
15. The formula will now look like =CONCATENATE("E18, ","E19, ","E20, ","E21, ","E22, ","E23, ","E24, ","E25, ","E26, ","E27, "
16. Select the entire formula, including the = sign, and hit F9
17. Hit Enter
18. Done!

Note: this only works for up to 256 references. Any more and the formula will thrown an error. If there's any way to change it so it will work on more than that, please let me know.
Nov 12 '18 #1
2 4799
12,516 Expert Mod 8TB
This question has been moved to the Excel forum.
Nov 13 '18 #2
1,047 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2.     If Selection.Cells.Count > 256 Then
  3.         MsgBox ("Too much cells selected ...")
  4.     Else
  5.         'Do your stuff here....
  6.     End If
  7. End Sub
You should let the user select the cells, and than run the macro.
It will notify the user if more than 256 cells are selected.
Nov 17 '18 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

by: geronimo_me | last post by:
Hi, I have the following code in an access module: Sub Run_Excel_Macro() Dim xls, xlWB As Object Dim strFile, strMacro As String
by: ghadley_00 | last post by:
Hi, I have a MS access database table for which I regularly need to import fixed width text data. At present I have to to cut and paste the text data from its source to a text file, save the...
by: derelict | last post by:
Hey all, im getting desperate now. I have a macro running in Word 2003, when I run the macro it *should* put a 'bottom' cell border in each cell that has the style used - this included a border at...
by: Jay Ruyle | last post by:
I'm trying to figure out a way to list several items in a listbox and let the user select any number of items in the listbox. I have tried to code in the items as bitwise items but all it stores...
by: jl2886 | last post by:
Is there a way to create a macro that, based on certain criteria(disposition status and date received), removes a line from one table and adds it to another.
by: Emmanouil | last post by:
Goodmorning to every body!!!!! My "problem" for 2day is this: I have a column in a access database with 50 cells, let us say. All cells are filled with 1 and 0 quite like these: 1 0 0 1 1 0
by: daoxx | last post by:
Hi I didn't find anything with the search - the words are too general. How can I create a macro and define what it does from code? EDIT: The other question was To do this use...
by: Stratocaster | last post by:
Hello, and thank you for any help in advance. I need help determining if any commands exist in VB (Excel macro style) that can enable a user to select cells and run a macro which performs...
by: jimatqsi | last post by:
I am working in Access 2003. I have written some code to add a context menu to a Treeview. I have a working right-click menu, I right-click on a node, I get my options and I choose "Display Details"...
by: USTRAGNU1 | last post by:
Good Day, I created a macro in Excel that works great. It formats multiple sheets exactly as expected, but when it completes it seems like I have to close out four or five worksheets before I can...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.