VBA for UDF to add to data range vs table?

[sza78]

Hi, I just recently started using VBA and discovered this community. looking forward to picking up new tricks.
I have built a user form for adding attendance to events using comboboxes to prevent errors. i'm using vlookups in 2 columns for calculating payments to attendees. having difficulties with the formulas copying from the above cell (col 8,9)

Private Sub CmndAdd_Click()
  Dim 1Row As Long
  Dim ws As Worksheet
Set ws = Worksheets("Attendance")

'find first empty row in database
1Row = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'copy the data to the database
With ws
  .Cells(1Row, 1).Value = Me.CboMaster.Value
  .Cells(1Row, 2).Value = Me.CBoEvent.Value
  .Cells(1Row, 3).Value = Me.CboRole.Value
  .Cells(1Row, 4).Value = Me.CboBasis.Value
  .Cells(1Row, 5).Value = Me.CBoSpeakerFee.Value
  .Cells(1Row, 6).Value = "Pending"
  .Cells(1Row, 7).Value = "Invited"
  .Cells(1Row, 8).Resize(2, 1).Formula = Cells(1Row - 1, 8).Formula
  .Cells(1Row, 9).Resize(2, 1).Formula = Cells(1Row - 1, 9).Formula
   
End With

End Sub
[code]
the code as i have it copies the formula from above and adds another row on 1row+1. so my formulas are offset by a row which also impact where the next row gets added to the data range.

the vlookup that i'm using in column 8 (and 9): 
IF(Col8="attending", VLOOKUP(SpeakerFee,fee array,2,FALSE),0)

Thanks for your help!

 

[Fluff]

Not entirely sure what your problem is, but try

.Cells(lRow, 8).Resize(1, 1).Formula = Cells(lRow - 1, 8).Formula
.Cells(lRow, 9).Resize(1, 1).Formula = Cells(lRow - 1, 9).Formula

 

[sza78]

thanks! i was able to get it to works using a macro to auto-fill the formulas in those columns, but i will try this as well!