moving to the next line in the appropriate column using VBA code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pama - 28 Nov 2007 17:10 GMT

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.

Reply to this Message

Jim Thomlinson - 28 Nov 2007 17:28 GMT

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

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 3 lines]
> that makes sense.
> Thank you.

Reply to this Message

pama - 28 Nov 2007 17:34 GMT

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

> So do you want the first blank line in column A??? If that is the case then
> something like this
[quoted text clipped - 11 lines]
> > that makes sense.
> > Thank you.

Reply to this Message

Jim Thomlinson - 28 Nov 2007 17:46 GMT

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

Signature

HTH...

Jim Thomlinson

> Thanks for responding. No not necessarily. If my active cell is J2, I'd like
> the address to continue in J3. Not A3.
[quoted text clipped - 14 lines]
> > > that makes sense.
> > > Thank you.

Reply to this Message

JLGWhiz - 28 Nov 2007 17:42 GMT

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.

Reply to this Message

pama - 28 Nov 2007 18:12 GMT

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

> 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
[quoted text clipped - 16 lines]
> > that makes sense.
> > Thank you.

Reply to this Message

JLGWhiz - 28 Nov 2007 20:31 GMT

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>

> That worked! I love it! Thanks to both.
> Where do you get a list of these "functions" that you can use?
[quoted text clipped - 19 lines]
> > > that makes sense.
> > > Thank you.

Reply to this Message