Macro For Inserting a Formula In Active Cell - Needs pop up to choose cell in Formula

[uolymme637]

Hi all!
I am looking for help creating a macro that helps with the following:

I have a date field that comes out of my ERP that is in the following format MDDYYYY or MMDDYYYY (Depending on the month). Aka there is no leading zero for Jan-Sept.

I have solved this issue by creating the following formula, when the date is in C3
=IF(LEN(C3)=7,LEFT(C3,1)&"/"&MID(C3,2,2)&"/"&RIGHT(C3,4),LEFT(C3,2)&"/"&MID(C3,3,2)&"/"&RIGHT(C3,4)) - And then doing text to columns to get it formatted to a date

However I would like to create a macro that lets me put this formula in the active cell, but the date that I am using is not always in C3 (It is always in column C). Is there a way I can create a pop up, or tell it to use column C for the row it is currently in?

 

[Fluff]

Hi & welcome to MrExcel.
You could use this simpler formula, which doesn't need text to columns

+Fluff New.xlsm
	B	C	D
1
2		1132020	13-Jan-20
3		7132020	13-Jul-20
4		11132020	13-Nov-20
Data

Cell Formulas
Range	Formula
D2:D4	D2	=DATE(RIGHT(C2,4),LEFT(C2,LEN(C2)-6),MID(C2,LEN(C2)-5,2))

 

[Fluff]

If my formula works for you, then try

Sub uolymme()
   Selection.Offset(, 1).FormulaR1C1 = "=DATE(RIGHT(rc[-1],4),LEFT(rc[-1],LEN(rc[-1])-6),MID(rc[-1],LEN(rc[-1])-5,2))"
End Sub

Just select the cells in col C you want it to work on.

 

[uolymme637]

Thank you! It works perfect as long as I have column D open

 

[Fluff]

Do you want it in a column?

 

[uolymme637]

Nah I can always add column D and that will be best. Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.