Try
Sub FormulaToEndOffset1SAS()
Dim lr As Long
With ActiveCell
lr = Cells(.Row, .Column).End(xlDown).Row
With Range(Cells(.Row, .Column + 1), Cells(lr, .Column + 1))
.Formula = _
"=IF(ISERR(DATE(LEFT(A" & .Row & ",4),MID(A" & .Row & ",5,2)," & _
"RIGHT(A" & .Row & ",2))),"""",DATE(LEFT(A" & .Row & ",4), " & _
"MID(A" & .Row & ",5,2),RIGHT(A" & .Row & ",2)))"
.NumberFormat = "dd/mmm/yyyy"
.Value = .Value
End With
End With
End Sub
‘============
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ankur
Sent: Tuesday, June 12, 2012 5:50 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Date format change by running the macro
hi Experts
i use this code to solve the query with slight modification,i want date format
in adjacent cell..
i used Macro for this..
But problem is ,this code can run upto specific no. of row i.e 475....
i want to be it a dynamic ...i.e it should work with based on selection of
range only....like solution of Krishna Sir is doing..what modification is
needed in my codes
File is attached for your reference
thanks in advance
Sub ChgMyFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERR(DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))),"""",DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A475")
ActiveCell.Range("A1:A475").Select
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.NumberFormat = "dd/mmm/yyyy"
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
ActiveCell.Offset(0, -1).Range("A1").Select
End Sub
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:32 PM, Ashish Bhalara <ashishbhalar...@gmail.com>
wrote:
Thank you Mr.Krish it is exactly what i want.
On Tue, Jun 12, 2012 at 2:21 PM, ankur <ankurpande...@gmail.com> wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.....Thanks for correcting
me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:20 PM, ankur <ankurpande...@gmail.com> wrote:
Hi Ahmed
Sorry for this ...Now i had changed the settings.....Thanks for
correcting me..
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 2:11 PM, Ahmed Honest <ahmedhon...@gmail.com>
wrote:
Dear Ankur,
Please if you don't mind will you make your font sytle and size as a
simple Or plain one. It looks too ODD.
Thanks for your understanding.
Regards,,,,,
On Tue, Jun 12, 2012 at 11:38 AM, ankur <ankurpande...@gmail.com> wrote:
HI ASHISH
I TRIED THIS CODE FOR YOUR SOLUTION ,BUT IT IS NOT SO GOOD....because
i dont know how to stop the code after the blank cells....Experts will guide me
in this issue how to remove this flaw....file is attached for your reference
Sub chgDateFormat()
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A500")
ActiveCell.Range("A1:A1").Select
End Sub
Regards:
CMA Ankur Pandey
Visit My Site
On Tue, Jun 12, 2012 at 1:04 PM, Ashish Bhalara
<ashishbhalar...@gmail.com> wrote:
Dear sir,
I have data in which date written in 20120412 format and i want to
make macro to which transfer the format to 12-Apr-2012 by running the macro on
selection range. Please help me in my below code.
Sub Macro1()
Dim selct As Range
selct = Range.Select
Range = WorksheetFunction.TEXT(TEXT(selct, "0000-00-00"),
"DD-MMM-YYYY")
End Sub
Thanks & regards.
Ashish Bhalara
9624111822
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any
security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this
forum in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum
in signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook.
Forum owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
Ahmed Bawazir
احمد باوزير
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
Thanks & regards.
Ashish Bhalara
9624111822
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to
mailto:excel-macros%2bunsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
