FillAdjacentCell.Formulas = True in User Form
 

Hello,
I have created a user form in Excel. Now i want to fill the formulas in the
adjacent of the table whenever i enter the data in User defined Form. I know
this is possible but don't know how?. I am providing herewith the coding of
the user form. Could anybody help to solve this issue.

Private Sub cmdAdd_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("DBASE")

irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

If Trim(Me.txtInv.Value) = "" Then

Me.txtInv.SetFocus

MsgBox "Please enter the Invoice number"

Exit Sub

End If
ws.Cells(irow, 1).Value = Me.txtInv.Value
ws.Cells(irow, 2).Value = Me.txtDate.Value
ws.Cells(irow, 3).Value = Me.cboCust.Value
ws.Cells(irow, 4).Value = Me.cboImCode.Value
ws.Cells(irow, 5).Value = Me.txtQty.Value
ws.Cells(irow, 6).Value = Me.txtPack.Value
ws.Cells(irow, 7).Value = Me.txtRate.Value
ws.Cells(irow, 8).Value = Me.txtContainer.Value
ws.Cells(irow, 9).Value = Me.txtSeal.Value
ws.Cells(irow, 10).Value = Me.optAir.Value
ws.Cells(irow, 10).Value = Me.optSea.Value

Me.txtInv.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Value
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
Me.cboCust.Value = ""
Me.cboImCode.Value = ""
Me.txtQty.Value = ""
Me.txtPack.Value = ""
Me.txtRate.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 6).Value
Me.txtContainer.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 7).Value
Me.txtSeal.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 8).Value
If optSea = True Then
ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 9).Value = "SEA"
ElseIf optAir = True Then
ActiveCell.Offset(0, 9).Value = "AIR"
End If

Me.txtInv.SetFocus
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_queryclose(cancel As Integer, _
closemode As Integer)
If closemode = vbFormControlMenu Then
cancel = True
MsgBox "Please use the button"
End If
End Sub
Private Sub UserForm_Initialize()
Dim cCust As Range
Dim cParts As Range
Dim ws As Worksheet
Set ws = Worksheets("DBASE")
Set wss = Worksheets("Cust Master")
Set wsss = Worksheets("Im Master")
Me.txtInv.SetFocus

For Each cCust In wss.Range("CustID")
With Me.cboCust
.AddItem cCust.Value
.List(.ListCount - 1, 1) = cCust.Offset(0, 1).Value
End With
Next cCust

For Each cParts In wsss.Range("PartsID")
With Me.cboImCode
.AddItem cParts.Value
.List(.ListCount - 1, 1) = cParts.Offset(0, 1).Value
End With
Next cParts

Me.txtInv.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Value
Me.txtDate.Value = Format(Date, "dd/mm/yyyy")
Me.cboCust.Value = ""
Me.cboImCode.Value = ""
Me.txtQty.Value = ""
Me.txtPack.Value = ""
Me.txtRate.Value = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 6).Value
Me.txtContainer.Value = ""
Me.txtSeal.Value = ""

Me.txtInv.SetFocus
End Sub

--
Vikram Dhemare