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

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

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)


wrote in message
oups.com...
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