Macro to format date and filldown

[tj4birdie]

Hi guys - here's my issue. Creating a Macro to convert a standard 8digit numeric into date format per below. What I'd like to do is change the 'range', 'columns', 'selection' to be a generic variety to be used in any cell/range/selection

macro should format cell directly to the left, then fill down
can anyone help me with the code? many thanks


Sub AS400_Date_Format()
'
' AS400_Date_Format Macro
' Format 8 digit AS400 date
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]=99999999,DATE(2222,2,2),DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
Range("BA2").Select
Columns("BA:BA").ColumnWidth = 9.43
Selection.AutoFill Destination:=Range("BA2:BA3940")
Range("BA2:BA3940").Select
End Sub

 

[Norie]

How would you determine what the range was to apply this to?

Would it be based on the current selection?

By the way, have you tried other methods to convert the dates to 'real' dates?

 

[tj4birdie]

ideally, i'd just like to change 1 cell and then fill down. I can create a function to do this, but would prefer if a macro could complete the multiple steps.

range and columns will change depending on data

 

[Norie]

I don't quite follow, you want to trigger the code by changing a cell?

What cell would that be and what change would be made?

Are you trying to convert some imported data so the dates are recognised as such by Excel?

 

[tj4birdie]

yes, trying to convert imported data

Bad date can appear in any column depending on the query.
Good date (macro date) would be in Row 2, Column +1

Range can be any length depending on the query.
I'd like to fill down rather than fill range but not sure if this is possible. I figured Fill down should detect the appropriate range

 

[tj4birdie]

Anybody else have any ideas?

data in cell A2:A?, result in B2:B?
macro to fill down?