S
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
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