Offset question

[mrowe]

Sub row_inserter()
I got the code below from a colleague to insert a row.

I'm trying to understand how it all fits together and was wondering if someone could explain how/ what the Offset command does, as it seems to be something pretty common.

Thanks in advance.
Matt


Range("A1").Select
Do Until ActiveCell = ""
If ActiveCell.Offset(1, 0) <> "" Then
ActiveCell.Offset(1, 0).EntireRow.Insert
ElseIf ActiveCell.Offset(1, 0) = "" Then
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub

 

[Derek]

Hi
The offset function is a way of referring to a cell, or range, that is "offset" from the cell or range in question. Usually it is applied to the activecell. The amount of offset is shown within brackets, the first number refers to number of rows (positive is down, negative is up) and the number after the comma refers to the number of columns (positive is right, negative is left) to move from the active (or reference) cell. So ActiveCell.Offset(1, 0) refers to the cell in the row immediately below the active cell but in the same column as the active cell.
Hope this makes sense
regards
Derek
This message was edited by Derek on 2002-03-07 00:52

 

[Ivan F Moala]

'Activates A1 in the Activesheet
Range("A1").Select

'Start of a do loop where this loop routine
'will loop until the activecell = ""
Do Until ActiveCell = ""
'If the activecell offset by ([1] Row down ,[0]on the same column)
'does not = "" then
If ActiveCell.Offset(1, 0) <> "" Then
'select this the cells Row and insert an entire row
ActiveCell.Offset(1, 0).EntireRow.insert
'otherwise...if the activecell offset by ([1] Row down ,[0]on the same column)
'equals ""....
ElseIf ActiveCell.Offset(1, 0) = "" Then
'then select this cell
ActiveCell.Offset(1, 0).Select
End If
'Now loop = goto the the start of the loop and evaluate the loop condition
Loop

End Sub

'NB: just inserts a row @ A2 ??? any use ??

ivan

 

[Dave Hawley]

Hi mrowe

What is you are wanting to do? The code you have will only insert 1 row at row 2!

 

[mrowe]

Hi Dave,

I know the code only inserts a row, but it was more to get an understanding of how it works so I can apply it to other routines.

Cheers
(ps - are you from Australia?)
Matt

 

[Dave Hawley]

Hi Matt

Yes I am! is my accent that strong )

 

[mrowe]

No so, just your website.... I'm from there also but live/work in London.

Always interested in work at home - got any tips on that??? :wink:

 

[Dave Hawley]

I worked in London myself for a year about 13 years ago (god I am getting old). Loved and hated it! Loved London, hated getting on the Tube every day.

What sort of work do you do and where abouts in Oz you from? I'm about 200 km south of Perth.

 

[mrowe]

I'm from Victoria, but lived in QLD and have been here for about 6 years.
I'm called a Business Analysist.... what ever... but we look after the companies Sales Order system, finance system (CODA) and Client Mgmt System along with Business Objects Reporting, Sybase stuff and SQL7 - also do some on the side web design and excel projects for internal things.

Thinking about comming home next year with the wife but don't know what the work situation is like.....

 

[Dave Hawley]

As far as I'm aware the enconomy is booming along at the moment. Not too sure what the job market is like though as I'm self-employed.