activecell giving me a hard time

[LetMeDoIt]

Greetings,

I'm using office 2003 in Excel VBA. ActiveCell.Offset() is used to
write to various cells, moving along columns, in another sheet, ie.
write to cell B1, then C1, then D1, etc. This works well.

The problem I have is I then try to set the active cell to the next
row on column A, but get an error msg. I do not want to using
ActiveCell.Offset() cause I do not know how many columns I wrote to (I
could find out, but that defeats the purpose of understanding the
issue).

I tried various statements, all giving me an error msg:

Range("'" & sheetName & "'!B1").Value = "NEW VALUE HERE" or
Range("A1").Value = "NEW VALUE HERE"

This does not give me an error msg, but jumps 6 rows down from where
the current active cell is:
ActiveCell.Range("A6").Select

Any help is greatly appreciated...
CG

 

[Rick Rothstein]

How are you using the ActiveCell.Offset() to move from column to column? I
mean by that, are you doing this...

ActiveCell.Offset(,1).Select

If so, don't... just increment the offset amount without doing the
selection. If you do it that way, then the ActiveCell will not have moved
and then this...

ActiveCell.Offset(1).Select

will work as you expect. However, I wouldn't even do it that way. I would
simply use a double loop to move column to column and then row to row.
Something like this...

ActiveCell.Offset(RowOffset, ColOffset)

either Set'ting it equal to a range variable to be used within the loop or
directly specifying the property to reference (it kind of depends on what
you are actually doing to the cells you visit which method to use).

 

[JLGWhiz]

In the VBA help file, See: How to reference cells and ranges

If it is not in there, it wont't work.

 

[LOFE]

You should just be able to use

ActiveCell(2,1).Select

which will move the focus down to the next row in the same column. The
Activecell command is Activecell(Row,Column) where Activecell(1,1) is the
current position. So if you're in A1, Activecell(2,1) moves to A2, and
Activecell(1,2) moves to B1 and so on.

You can also use it to enter values:

Activecell(2,1) = "This text"

Or it can be a variable:

MyValue = Range("A3")
Activecell(1,2) = MyValue

And similarly, you can use it to get values:

MyValue = Activecell(3,1) 'Gets the value three rows down and stores it as a
variable.

Hope this helps.

 

[LetMeDoIt]

How are you using theActiveCell.Offset() to move from column to column? I
mean by that, are you doing this...

   ActiveCell.Offset(,1).Select

If so, don't... just increment the offset amount without doing the
selection. If you do it that way, then theActiveCellwill not have moved
and then this...

   ActiveCell.Offset(1).Select

will work as you expect. However, I wouldn't even do it that way. I would
simply use a double loop to move column to column and then row to row.
Something like this...

   ActiveCell.Offset(RowOffset, ColOffset)

either Set'ting it equal to a range variable to be used within the loop or
directly specifying the property to reference (it kind of depends on what
you are actually doing to the cells you visit which method to use).

--
Rick (MVP - Excel)












- Show quoted text -

Many thanks for all your help, I used Rick's suggestion and it worked
like a charm.
regards,
CG