Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell giving me a hard time
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell giving me a hard time
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). -- Rick (MVP - Excel) "LetMeDoIt" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell giving me a hard time
In the VBA help file, See: How to reference cells and ranges
If it is not in there, it wont't work. "LetMeDoIt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell giving me a hard time
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. "JLGWhiz" wrote: In the VBA help file, See: How to reference cells and ranges If it is not in there, it wont't work. "LetMeDoIt" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
activecell giving me a hard time
On Apr 22, 5:15*pm, "Rick Rothstein"
wrote: 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) "LetMeDoIt" wrote in message ... 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, allgivingmean error msg: Range("'" & sheetName & "'!B1").Value = "NEW VALUE HERE" *or Range("A1").Value = "NEW VALUE HERE" This does not givemean error msg, but jumps 6 rows down from where the current active cell is: ActiveCell.Range("A6").Select Any help is greatly appreciated... CG- Hide quoted text - - Show quoted text - Many thanks for all your help, I used Rick's suggestion and it worked like a charm. regards, CG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hard time with If InStr(1, .. | Excel Programming | |||
time sheets giving a minus | Excel Discussion (Misc queries) | |||
ActiveCell giving blank value | New Users to Excel | |||
ActiveCell giving blank value in vbscript | Excel Worksheet Functions | |||
Name of shape giving VB run-time error | Excel Programming |