Insert formula programatically

PRA007 · #1 11-03-2015, 01:10 AM

I would like to insert a specific formula programmatically.

I have excel document like this(sheet1.xlsx).

Code:

4403894	19910915	Patent Expired for Failure to Pay Maintenance Fees.
4403894	19910416	Maintenance Fee Reminder Mailed.
4403894	19860915	Payment of Maintenance Fee, 4th Year, PL 96-517.
4583446	19980419	Patent Expired for Failure to Pay Maintenance Fees.
4583446	19980213	Maintenance Fee Reminder Mailed.
4583446	19931004	Payment of Maintenance Fee, 8th Year, Large Entity.
4583446	19910723	Payor Number Assigned.

I would like to convert this to following format(finalsheet1.xlsx).

Code:

4403894	19910915	Patent Expired for Failure to Pay Maintenance Fees.	Sep 15, 1991	Sep 15, 1991: Patent Expired for Failure to Pay Maintenance Fees.
4403894	19910416	Maintenance Fee Reminder Mailed.	                Apr 16, 1991	Apr 16, 1991: Maintenance Fee Reminder Mailed.
4403894	19860915	Payment of Maintenance Fee, 4th Year, PL 96-517.	Sep 15, 1986	Sep 15, 1986: Payment of Maintenance Fee, 4th Year, PL 96-517.
4583446	19980419	Patent Expired for Failure to Pay Maintenance Fees.	Apr 19, 1998	Apr 19, 1998: Patent Expired for Failure to Pay Maintenance Fees.
4583446	19980213	Maintenance Fee Reminder Mailed.	                Feb 13, 1998	Feb 13, 1998: Maintenance Fee Reminder Mailed.

I have formula as follow

Code:

Column4: =DATEVALUE(TEXT(B1,"0000-00-00"))
Column5: =TEXT(D1,"Mmm dd, yyyy")&": "&C1

As a part of workflow I would like to insert above formula in excel file automatically.

I want to run code from third macro enabled XLSM doc assuming it Shee1.xlsx closed.

Files (as usual) are at:
https://sites.google.com/site/rtsk2015/fo
Download: Shee1.xlsx
Download: FinalShee1.xlsx

macropod · #2 11-03-2015, 03:19 AM

Is there a reason for not using something like:
=TEXT(DATEVALUE(TEXT(B1,"0000-00-00")),"MMM DD, YYYY")&": "&C1
in column D?

For example:

Code:

Sub Demo()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
With ThisWorkbook.Worksheets("Sheet1").UsedRange
  For i = 1 To .Cells.SpecialCells(xlCellTypeLastCell).Row
    .Cells(i, 4).Value = "=TEXT(DATEVALUE(TEXT(B" & i & "," & _
      Chr(34) & "0000-00-00" & Chr(34) & "))," & Chr(34) & _
      "MMM DD, YYYY" & Chr(34) & ")&" & Chr(34) & ": " & Chr(34) & "&C" & i
  Next
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

PRA007 · #3 11-03-2015, 03:38 AM

No reason. Just lack of knowledge.

PRA007 · #4 12-04-2015, 04:36 AM

As this has definitely answered my question, can be marked as solved.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Removing add in programatically	chrismad	Word VBA	4	05-14-2014 02:56 PM
How do I send an email programatically?	macroscope	Outlook	7	02-01-2013 02:27 PM
Insert the content of a cell into formula?	chaghrie	Excel	3	08-27-2012 05:22 AM
Sorting or an Excel formula to insert a blank row after ending a series.	PRADEEPB270	Excel	1	09-07-2011 02:33 AM