I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

So do you want the first blank line in column A??? If that is the case then
something like this

with sheets("Sheet1")
..select
..cells(rows.count, "A").end(xlUp).offset(1,0).select
end with
--
HTH...

Jim Thomlinson


"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

Thanks for responding. No not necessarily. If my active cell is J2, I'd like
the address to continue in J3. Not A3.

"Jim Thomlinson" wrote:

So do you want the first blank line in column A??? If that is the case then
something like this

with sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlUp).offset(1,0).select
end with
--
HTH...

Jim Thomlinson


"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

look at JLGWhiz's suggestion in that case...
--
HTH...

Jim Thomlinson


"pama" wrote:

Thanks for responding. No not necessarily. If my active cell is J2, I'd like
the address to continue in J3. Not A3.

"Jim Thomlinson" wrote:

So do you want the first blank line in column A??? If that is the case then
something like this

with sheets("Sheet1")
.select
.cells(rows.count, "A").end(xlUp).offset(1,0).select
end with
--
HTH...

Jim Thomlinson


"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

In addition to Jim's suggestion, you might want to use the offset method. If
you are "somewhere" on the sheet and want to move to the cell beneath where
the cursor is then:

ActiveCell.Offset(1, 0).Activate 'or Select (I perfer activate for single
cells)

You can use Offset to move the cursor in any direction and for as many cells
as desired, so long as there are cells available to move to. If the cursor
is in cell A1, you cannot move up or left, it will draw an error message
because there are no cells available in those directions. So you have to
have a general idea where your cursor is when you use offset.

"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

That worked! I love it! Thanks to both.
Where do you get a list of these "functions" that you can use?

"JLGWhiz" wrote:

In addition to Jim's suggestion, you might want to use the offset method. If
you are "somewhere" on the sheet and want to move to the cell beneath where
the cursor is then:

ActiveCell.Offset(1, 0).Activate 'or Select (I perfer activate for single
cells)

You can use Offset to move the cursor in any direction and for as many cells
as desired, so long as there are cells available to move to. If the cursor
is in cell A1, you cannot move up or left, it will draw an error message
because there are no cells available in those directions. So you have to
have a general idea where your cursor is when you use offset.

"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.

Where do you get a list of these "functions" that you can use?

Mostly from the VBA help files. In xl2003, the VB editor help has three
reference documents. Browse through them when you have some time to kill and
you will be surprised what you will find there. Back in 1995, that was all I
had to help me learn how to use VBA. Every time I found a section of the
files that looked like something I could use, I would print it. Eventually,
I had a pretty good reference document that I could read pretty easily and I
set up tests to try different things until I was confident that I could put
together a workable macro. After that, it was all up hill. <g

"pama" wrote:

That worked! I love it! Thanks to both.
Where do you get a list of these "functions" that you can use?

"JLGWhiz" wrote:

In addition to Jim's suggestion, you might want to use the offset method. If
you are "somewhere" on the sheet and want to move to the cell beneath where
the cursor is then:

ActiveCell.Offset(1, 0).Activate 'or Select (I perfer activate for single
cells)

You can use Offset to move the cursor in any direction and for as many cells
as desired, so long as there are cells available to move to. If the cursor
is in cell A1, you cannot move up or left, it will draw an error message
because there are no cells available in those directions. So you have to
have a general idea where your cursor is when you use offset.

"pama" wrote:

I am a beginner and would like to create a macro that inserts an address into
a worksheet. It works fine if I am in cell A1, because the code calls up
cells A2 and A3 with: Range("A2").Select or Range("A3").Select.
What code do I use to make the next line of the address go to the cell under
the first line of the address no matter where I am on the spreadsheet. I hope
that makes sense.
Thank you.