Fully tested on provided file.
The lr is the last populated row in column A and was based on the file
presented. It is designed so that whatever cell you select in col A then column
B ONLY to the lr will be filled with the formula and the formatting. If not,
send your file personally to me...
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: ankur
Sent: Tuesday, June 12, 2012 11:22 AM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Date format change by running the macro
Thanks Don Sir
Very Nice Code....
But it is not limited to Selected Range....full column is converted to desired
format...
is there any way to limit the code upto selected range only
Regards:
CMA Ankur Pandey
On Tue, Jun 12, 2012 at 6:30 PM, dguillett1 <dguille...@gmail.com> wrote:
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
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
--
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
