This may seem rudundant...
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
|