This may seem rudundant...

[stussymo]

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

 

[keepITcool]

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

 

[Bob Phillips]

I assume it is this line that is offending

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

You don't say what you are trying to do wwith the formula, so this is
somewhat of a guess

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

and add this function

'-----------------------------------------------------------------
Function ColumnLetter(Col)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)