Different beginning cell each time for same macro

L

lclc02

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.
 
D

Debra Dalgleish

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
 
J

jim

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
 
V

vamosj

::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:
:
 
Top