CODEWITHSUNDEEP
excelvba
won_g
won_g

Reputation: 17

Dynamically add a formula with my Excel userform everytime I hit submit

Here is the code I am using for my submit button 

Private Sub cbsubmit_Click()
Dim emptyRow As Long
Sheets("ComplaintsData").Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
Cells(emptyRow, 2).Value = emptyRow - 1
Cells(emptyRow, 1).Value = dtdate.Value
Cells(emptyRow, 3).Value = cmbSource.Value
Cells(emptyRow, 4).Value = cmbIssue.Value
Cells(emptyRow, 5).Value = cmbcomptype.ValueC
Cells(emptyRow, 6).Value = tbname.Value
Cells(emptyRow, 7).Value = ccdemail.Value
Cells(emptyRow, 8).Value = ccdphone.Value
Cells(emptyRow, 9).Value = cmbRegion.Value
Cells(emptyRow, 10).Value = tbreferred.Value
Cells(emptyRow, 11).Value = tbmanaged.Value
Cells(emptyRow, 12).Value = tbaction.Value
Cells(emptyRow, 13).Value = tbObjLink.Value
Cells(emptyRow, 14).Formula = "=TEXT(DATE(YEAR(emptyrow,1),MONTH(emptyrow,1),DAY(emptyrow,1)),""mmm""& "" "" & ""yyyy"")"

Everything works until the bit where I want to add a formula. What I am after is everytime I click submit, the formula is populated in column N based on date on column A and the row it is associated to.

Hope someone can help me resolve this?

Upvotes: 0

Views: 63

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

Something like this:

Cells(emptyRow, 14).Formula = "=TEXT(A" & emptyRow & ", ""mmm yyyy"")"

If the value in ColA is already a date there's no need to reconstruct it using DAY/MONTH/YEAR

Upvotes: 1

Related Questions