Private Sub Worksheet_Change(ByVal Target As Range)
With Target.EntireRow.Cells(1)
If .Row = 1 And .Value = "" Then
Application.EnableEvents = False
.FormulaR1C1 = "=R[-1]C"
.Formula = .Value
Application.EnableEvents = True
End If
End With
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


wrote :

I've browsed and seen many posts on how to use VBA to write Formulas
to cells and I am sure I'm doing everything correctly, but still I
get an error: "application-defined or object-defined error". I hope
someone can give me some clues. Thanks in advance! -Eric
p.s. I realize this code isn't very efficient right now. I'll fix
that later, but just want to get it working for now.

==============CODE================
Private Sub Worksheet_Change(ByVal Target As Range)
'If Row was just inserted, first cell in row will be blank -
'This function writes a formula into the empty cell to continue the
'auto-numbering
Dim curRow As String
Dim curCol As String
Dim cellFormula As String

On Error GoTo Err_Worksheet_Change

Cells(Target.Row, 1).Select

curRow = Str(ActiveCell.Row)
curRow = Trim(curRow)
'curRow = "$" & curRow

curCol = "A"
'curCol = "$" & curCol

cellFormula = "=Offset(" & curCol & curRow & ", -1, 0) + 1)"

If Cells(Target.Row, 1).Value = "" Then
ActiveCell.Formula = cellFormula
End If

Err_Worksheet_Change:
MsgBox "Error " & Err.Number & ": " & Err.Description

End Sub