View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
[email protected] stussymo@yahoo.com is offline
external usenet poster
 
Posts: 1
Default This may seem rudundant...

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