ActiveCell problem? Data appearing in wrong row in spreadsheet

[groovygarden]

I've been asked (joy of joys) to modify someone's VB code.

There is a problem with one of the modules which works with an Excel spreadsheet. The module is designed to place a serial number in column A in the next row down from the previously active cell. However, instead of putting the serial number in the next row down, the serial number is appearing in column A, but about 33,000 rows down!

Does anyone have an idea why this is happening? The code is below.

Many thanks in advance

Sub installNew(serial)

' routine to install data onto the "New Installs" worksheet

Dim serialNew As String
serialNew = serial
Worksheets("New-Installs").Activate

Selection.SpecialCells(xlCellTypeLastCell).Select

' this finds last used cell

ActiveCell.Offset(1, -10).Value = serial

ActiveCell.Offset(1, -10).Activate
 ' this puts the serial number in the next row down and sets that cell as active

 

[ide]

try it:
1. create a new worksheet
2. type in the cell "D100" anything you want
3. Edit/Clear/All
4. go to the cell: a1
5. push Ctrl+End (Selection.SpecialCells(xlCellTypeLastCell).Select"
6. ...

ide

 

[ide]

If column A allways contains value, try this sub to move to end:

Sub sbGoToEmptyCell()
Dim i As Long
For i = 0 To 64000
If ActiveCell.Offset(i, 0) = "" Then
ActiveCell.Offset(i, 0).Activate
Exit Sub
End If
Next i
End Sub

i hope it helps you

 

[RobBroekhuis]

Seems to me like you always have the same column structure (hence you know to go 10 columns to the left to find the one you need). If that column is column A (for example), then you can go directly to the next unused row using:

range("A1&quot.end(xldown).offset(1,0).select

Only tricky bit is if there is only one row of values in your table - then this takes you all the way down to the bottom. Having two rows worth of header text solves this little glitch, or else you can simply test:

if range("A2&quot="" then _
range("A2&quot.select else _
range("A1&quot.end(xldown).offset(1,0).select

I hope this helps.
Rob

Rob