Different beginning cell each time for same macro

I would like my macro to begin in a different cell that I 
select each time and then have the first step of the macro 
select the 12 cells immediately to the right of the cell I 
select each time. 

Currently my macro runs perfectly except that the macro 
will only begin in the exact same cell every time the 
marco is run.  This means that the same 13 cells are used 
by the macro every time.  Where as I need the marco to 
begin in a different cell that I more or less randomly 
select and then the macro should select the 12 cells 
immediately to the right of that cell.

I know someone out there knows how to do this.  Your help 
would be appreciated.
0
lclc02 (1)
5/1/2004 1:11:41 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
790 Views

Similar Articles

[PageSpeed] 13

Before you start recording the macro, select a starting cell (it doesn't 
matter which cell you select)

Then, choose Tools>Macro>Record Macro
On the Stop Recording toolbar, click the Relative Reference button.
Select the 12 cells to the right of the active cell, and do the steps 
that you want to record.
Click the Relative Reference button
Click the Stop Recording button.

Now, when you run the macro, it will act on the 12 cells to the right of 
the active cell

lclc02@hotmail.com wrote:
> I would like my macro to begin in a different cell that I 
> select each time and then have the first step of the macro 
> select the 12 cells immediately to the right of the cell I 
> select each time. 
> 
> Currently my macro runs perfectly except that the macro 
> will only begin in the exact same cell every time the 
> marco is run.  This means that the same 13 cells are used 
> by the macro every time.  Where as I need the marco to 
> begin in a different cell that I more or less randomly 
> select and then the macro should select the 12 cells 
> immediately to the right of that cell.
> 
> I know someone out there knows how to do this.  Your help 
> would be appreciated.


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/1/2004 1:49:27 AM
I do not believe you can have a macro start, ask you to 
do something, then continue.  About the only thing you 
can do is create a button select your cell then click the 
button to run the code.  The command for selecting the 12 
cell to the right is:

Sub Select12CellToTheRight()

Dim BeginAdd, EndAdd
BeginAdd = ActiveCell.Address
EndAdd = ActiveCell.Offset(, 12).Address
Range(BeginAdd & ":" & EndAdd).Select

End Sub

If there's an easier way to do this I don't know what it 
is.  It never dawned on be until now but I've written 
hundreds of thousands of line in VBA and I've never had 
to do this.  I kept looking for an easier way but 
couldn't find it.

Jim




0
anonymous (74721)
5/1/2004 3:34:34 AM
::This might work for you.  Try using an inputbox  Make sure that you
range select is set the left upmost cell of the range you wish to use.
If your first set range starts at A5 then it will look like this.


Sub GetRange()
Start = Application.InputBox(\"Where would you like to start?\")
Range (\"A5\").Select
For i = (1+1) to Start
ActiveCell.Offset(1,0).Select
Next i
BegAdd = ActiveCell.Address
EndAdd=ActiveCell.Offset(,12).Address
Range (BegAdd &\":\"& EndAdd.Select
EndSub

What this should do is ask you how many rows you wish to go down.  I
stores it as \"Start\" then uses an offset within a loop until i
reaches your desired starting line.  Then going off of Jim's post yo
can use the BegAdd and EndAdd to help select the range of cells and
Viola!! you have your range of 12 selected.

Now if you wanted to start your range selection just to the right o
the active cell put this into the BegAdd line.

BegAdd=ActiveCell.Offset(1,).Address


Let us know how it goes.


J. Vamos  :cool: 
:

--
Message posted from http://www.ExcelForum.com

0
5/1/2004 5:53:30 AM
Reply:

Similar Artilces:

Can't receive after an amount of time
Windows XP Professional Roadrunner Connection Office XP Professional PC-Cillin Microsoft ActiveSync for Pocket PC (I sync calendar/tasks/notes only) I run Outlook 2003 and have several email addresses I check. Everything works but after awhile I get the following error from every email address (most from different companies): Task '*** - Receiving' reported error (0x80042108) : 'Outlook is unable to connect to your incoming (POP3) e-mail server. If you continue to receive this message, contact your server administrator or Internet service provider (ISP).' After res...

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

How to fill fill a column with numbers, beginning at number X, counting up.
I simply need to add numbers, beginning with 15,347, (counting upward), to a column with empty values in a table. Is there an easy way to do this, rather than completing it in excel and importing it, then attempting to update the table? Thanks for any suggestions. While I cannot imagine a legitimate use for this, the following code will insert rows starting at 15347 and going to 22000 Dim dbCurr As DAO.Database Dim lngLoop As Long Dim strSQL As String Set dbCurr = CurrentDb For lngLoop = 15347 To 22000 strSQL = "INSERT INTO MyTable (MyField) " & _ "VALUE...

Macro to change cell text color
OBJECTIVE: I select a range of cells. I want a macro I can run tha changes the color of the text based on 3 criteria: (1) if a numeric value w/o an = sign or any mathematical operators then RED (2) if a forumla w/ an = sign, but no other operators, then GREEN (3) if a formula w/ an = sign and operators, then BLUE Can anyone post this? Thanks. - Ji -- James ----------------------------------------------------------------------- James C's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1211 View this thread: http://www.excelforum.com/showthread.php?threadid=3953...

Default character at beginning of cell
Is there a way to have a character be automatically inserted into given cell before the information that a user types in? I nee something like a circle or square from Wingdings to be displayed at th beginning of a group of cells almost like a bullet in Microsoft Word. I don't want the user to have to manually enter the character befor typing in their data. Thanks for any help -- Message posted from http://www.ExcelForum.com Honda, If you're willing to install a macro, we can write you one that will do that. You'll need to say which columns or rows, etc., where this is to be...

Help w/ getting F2 into a macro
I have an Excel 2000 speadsheet with 9000 rows of data which someone imported from some other software. The one column should contain numeric data, but the import left it with some preceding spaces and a following asterisk. This needs to be converted to numerical format which can be referenced by formulae. I really don't want to plow through 9000 lines and am trying to set up a macro to do this. The following keystrokes have the right effect. F2 (function key to go to edit mode) Backspace (to delete the training asterisk character) Enter (which writes back to the cell a...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Ribbon X macro not available
I have created a Ribbon and everything was working fine until the last button was added then I got the macro not available error. I have all my trust settings wide open. Is there a limit to the number of items that can be added to a ribbon. Here is my XML The error happens on anything below Character Count <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" > <ribbon > <tabs > <tab id="Tab2" label="RPExcel07"> <group id="Group1" > <menu id="Menu...

protecting cells #2
How do you protect cells that contain a formula, and leave unprotected cells that will have data entered into it? Hi Ed! Select all cells in the worksheet by pressing the button at the intersection of rows and column headings. Format > Cells > Protection Remove check from "Locked" OK Select the cells you want to protect Format > Cells > Protection Place check in "Locked" OK Tools > Protection > Protect Sheet Give and confirm password if you want OK Note that it's a two stage operation. Format Protection followed by Tools Protection. To change, you...

Switchboard hangs when running a macro
Access 2003: I've recently discovered the Switchboard - a grand concept; wish it wasn't so hidden... When running a macro from a form, no problem. But when running the same macro from the Switchboard, Access hangs. It seems to run all of the steps in the macro, and finish, but then Access hangs. Any help would be appreciated. Thanks, Mike in Sugar Land, TX On Mon, 30 Nov 2009 21:28:03 -0800, Switchboard newbie <Switchboard newbie@discussions.microsoft.com> wrote: My crystal ball is broken. Can you post some code, stripped to the bare essentials? -Tom. ...

View lengthy text from one cell to another cell without widening c
View lengthy text from one cell to another cell without widening cell width of the other cell I used the = sign to display a long text in cell A1 in cell H1 so that if I change the text of A1, cell H1 will change as well; I want to keep the cell widths of the worksheet narrow; how can I view the entire text in H1 without having to widen it; whereas the formula bar in A1 shows the entire text, the formula bar shows only the formula reference, not the text itself On a related point, I downloaded a form template and linked it to my data; when I use the = sign to insert my data...

Page Load/Click Event Timing Issue
I have a webform that alters it's content depending on whether or not the current user is logged in. It does this in the Page_Load event. My master page has a logout button. If the user clicks this button, it fires the Page_Load event in my content page, the Page_Load event in my master page, and finally the Logout_Click event for my logout button. This means that my code that alters the page (depending on whether or not the current user is logged in) fires before the code that logs the user out. This results in serving a page for a logged-in user when they are now logged ...

Store sheet name in a cell on the sheet
I wish to store the sheet name in a cell on the sheet. I.E. Sheet Name is 14, Cell A2 to contain 14. Using Excel 2000. VBA would allow this: Cells(1,1).value = activesheet.Nam -- crispb ----------------------------------------------------------------------- crispbd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1088 View this thread: http://www.excelforum.com/showthread.php?threadid=27626 Enter this in any cell of an *already saved* sheet: =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) -- HTH, RD ============...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

Selecting Only Unprotected Cells
Hello, Excel 2000. I have a spreadsheet that I need to find all unprotected cells in and change some formatting options for those cells only. The sheet was designed by someone else and I think that if I try to find them with trial-and-error, it will either take me forever, or I will never find them all. I cant find any info so I am hoping someone here may know. Any ideas? Thanks, Chris Chris, you could use a macro to change the background color in them so you can find them easily, like this Sub Color_Unprotected_cells() 'will change the interior color to blue in all unprotecte...

COUNTIF formula Using 2 or more cells
Hi, I am just exploring more with Excel. I am going to paste the table in and explain what I am trying to acheive. A B 1 31/04/2010 (month of reporting, Displayed as April, 2010) 2 3 12/04/2010 Vocational Assessment 4 15/03/2010 Vocational Assessment 5 01/04/2010 Vocational Re-Education I am trying to Count how many Vocational Assessment have the Date in the month of April. Does anyone know the best formula to use. I have used =SUMPRODUCT((A3:A5="31/03/2010")*(B3:B4="Vocational Assessment...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Copying spinners for each cell in Excel 2003?
I have an inventory tracking sheet that keeps track of my inventory. I've added in a spinner control to increase/decrease a value in a certain cell. In my worksheet, the value is located in column E, and the spinner control is in column F. I have about 200 spinners to add, and need to fill down the links to it's adjacent cell. I found something related to checkboxes, but how do I adapt it for the spinner control? The first value is in E3, and the first spinner is in F3. Create 200 spiners in F3:F203 and link to E3:E203 Sub add_spinners_and_links() Dim myRng As Range Dim Spnr...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

Macro to coppy cells to certain rows depending on value in cell
I want his macro to after it have inserted the colmns and added the formula to 1. copy range A1 to E1 to every row where the word "Header" is in colmn F. 2. Then copy paste the whole sheet as values. 3. Then the range now standing left of "header" must be copied to the empy cells beneath each heading. For example a b c d e f 1)12/12/2005 F001 SAO3 1 CCE Header 2) Detail ...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

Two Text Lines in one Cell.
Is there is a way to have two text lines in one cell. Please advise. Thanks Peter Peter, When you type text in to a cell, use ALT+Enter to insert a line break. To combine two cells in to a single cell, you can use a formula like =A1&CHAR(10)&A2 . Ensure that the cell has Wrap Text formatting enabled. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Peter" <anonymous@discussions.microsoft.com> wrote in message news:1B6AFD9D-5C93-46D1-8B00-A2919A3659F8@microsoft.com... > Is there is a way to have two text lines...

2006 Sick time accrual problem
My users are experiancing problems with Sick time accrual for Salaried employees. The hours are correct, but the dollers are not. The hourly employees are working fine, only salaried are wrong. This is our first run of the 2006 pay year, and we have applied all year end and 2006 updates. Any ideas of what could be wrong would be appreciated! Thanks! ...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...