Excel Macro/VBA- Paste formula into next available cell with criteria using a user form

[homevest]

Hello,

I cannot seem to figure this out! Maybe not even possible???

I am trying to use a user form that when I fill it out and hit submit it pastes the information in to the next available row - That part works fine.
What I cant seem to figure out is when this information pastes in it will also drag the formula from the cell above it on the last two columns which would be in Cq and Cr



Private Sub cmdAdd_Click()




Dim nextrow As Range
'error handler
On Error GoTo errHandler:
'set the next row in the database
Set nextrow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'check for values in the first 4 controls
For x = 1 To 7
If Me.Controls("R" & x).Value = "" Then
MsgBox "You must add all data"
Exit Sub
End If
Next
'check for duplicate Address
If WorksheetFunction.CountIf(Sheet2.Range("F:F"), Me.R4.Value) > 0 Then
MsgBox "This Address already exists"
Exit Sub
End If
'number of controls to loop through
cNum = 92
'add the data to the database
For x = 1 To cNum
nextrow = Me.Controls("R" & x).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the controls
For x = 1 To cNum
Me.Controls("R" & x).Value = ""
Next


'sort the database
'Sortit
'error block
On Error GoTo 0
Exit Sub
errHandler::
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"
End Sub

 

[Fluff]

How about

   For x = 1 To cNum
      nextrow.Offset(, x - 1) = Me.Controls("R" & x).Value
   Next
   nextrow.Offset(-1, 92).Resize(2, 2).FillDown

 

[homevest]

hmmm. Tried that but no luck

 

[Fluff]

What does "no luck" mean?
Did you get an error?
Did it work on the wrong?
??

 

[homevest]

What does "no luck" mean?
Did you get an error?
Did it work on the wrong?
??

It didnt do anything.

No error - doesnt pull down any other cell either that I can see

 

[Fluff]

In that case can you please post the code you are now using.

 

[homevest]

In that case can you please post the code you are now using.

Ive tried moving it in different spots.
Current placement copies and pastes everything entered from 1-92 rather than pulling down the formula from above

Private Sub cmdAdd_Click()


   
   
    Dim nextrow As Range
    'error handler
    On Error GoTo errHandler:
    'set the next row in the database
    Set nextrow = Sheet2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
    'check for values in the first 4 controls
    For x = 1 To 7
        If Me.Controls("R" & x).Value = "" Then
            MsgBox "You must add all data"
            Exit Sub
        End If
    Next
    'check for duplicate Address
    If WorksheetFunction.CountIf(Sheet2.Range("F:F"), Me.R4.Value) > 0 Then
        MsgBox "This Address already exists"
        Exit Sub
    End If
    'number of controls to loop through
    cNum = 92
    'add the data to the database
    For x = 1 To cNum
        nextrow = Me.Controls("R" & x).Value
        Set nextrow = nextrow.Offset(0, 1)
    Next
    
    'pull down formula
    For x = 1 To cNum
      nextrow.Offset(, x - 1) = Me.Controls("R" & x).Value
   Next
   nextrow.Offset(-1, 92).Resize(2, 2).FillDown
    
    Next
    'clear the controls
    For x = 1 To cNum
        Me.Controls("R" & x).Value = ""
    Next
          
    'sort the database
    'Sortit
    'error block
 
 
    
    On Error GoTo 0
    Exit Sub
errHandler::
    MsgBox "An Error has Occurred  " & vbCrLf & "The error number is:  " _
           & Err.Number & vbCrLf & Err.Description & vbCrLf & _
           "Please notify the administrator"
End Sub

 

[Fluff]

You need to remove this part of the code

   For x = 1 To cNum
        nextrow = Me.Controls("R" & x).Value
        Set nextrow = nextrow.Offset(0, 1)
    Next

Also when posting code please use code tags, the # icon in the reply window.

 

[homevest]

Dang it - of course! Rookie mistake on my part.
That worked beautifully

 

[Fluff]

You're welcome & thanks for the feedback