Thank a lots Asa, working very nice without any trouble. Regards,
Aamir Shahzad On Sun, May 20, 2012 at 7:39 AM, Asa Rossoff <a...@lovetour.info> wrote: > Hi Aamir, > > How about this: > > Private Sub Worksheet_Change(ByVal Target As Range) > > Application.EnableEvents = False > > MacroMillion Target > > Application.EnableEvents = True > > End Sub > > > > Private Sub MacroMillion(ByRef Target As Range) > > Const M As Double = 1000000 > > Const EvalMethodMinCellThreshold As Long = 50 > > Const Formula As String = > "IF(RIGHT(_,1)=""M"",IF(ISNUMBER(--LEFT(_,LEN(_)-1)),LEFT(_,LEN(_)-1)*" & M > & ",_),_)" > > Dim WS As Worksheet, TextRange As Range, Area As Range, Cell As Range > > Dim V As Variant, N As String, ScreenState As Boolean, ScreenStateTouched > As Boolean > > On Error Resume Next > > Set TextRange = Target.SpecialCells(xlCellTypeConstants, xlTextValues) > > On Error GoTo 0 > > If Not TextRange Is Nothing Then > > If TextRange.Cells.Count > 1 Then > > ScreenState = Application.ScreenUpdating > > If ScreenState Then > > Application.ScreenUpdating = False > > ScreenStateTouched = True > > End If > > End If > > Set WS = Target.Parent > > For Each Area In TextRange.Areas > > If Area.Cells.Count >= EvalMethodMinCellThreshold Then > > Area.Value2 = WS.Evaluate(Replace(Formula, "_", > Area.Address)) > > Else > > For Each Cell In Area > > V = Cell.Value2 > > If UCase$(Right$(V, 1)) = "M" Then > > N = Left$(V, Len(V) - 1) > > If IsNumeric(N) Then > > Cell.Value2 = N * M > > End If > > End If > > Next Cell > > End If > > Next Area > > If ScreenStateTouched Then > > If Application.ScreenUpdating <> ScreenState Then > > Application.ScreenUpdating = ScreenState > > End If > > End If > > End If > > End Sub > > > > The routine implements two different methods of transforming cell values > ending in "M" to millions. One method loops through pertinent cells using > traditional VBA functions. The other uses the Worksheet.Evaluate method to > update cell values that may need to be transformed using a worksheet > formula. The constant EvalMethodMinCellThreshold determines which method > will be used. If the number of cells changed at one time is at least > EvalMethodMinCellThreshold, then the Worksheet.Evaluate method is used. > For smaller numbers of cells, traditional VBA techniques are used. I > supplied a value of 50 to EvalMethodMinCellThreshold because that seemed > to be roughly the number of cells at which the Worksheet.Evaluate method > was fastest on my system (feel free to experiment, or even set to 1 to > see what happens if that method is always used). Worksheet.Evaluate is > very fast once it gets going, but it has significant overhead for each call. > > > > Asa > > > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *Aamir Shahzad > *Sent:* Saturday, May 19, 2012 10:39 AM > > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Convert amount to Million > > > > For example I insert the column in sheet it's taking too much time when > running this code otherwise inserting the column is too fast. Therefore I > ask you people for look & amend for this code. > > > > Regards, > > > > Aamir Shahzad > > > > On Sat, May 19, 2012 at 6:37 PM, Rajan_Verma <rajanverma1...@gmail.com> > wrote: > > *Can you make it more clear??* > > > > * * > > *Regards* > > *Rajan verma* > > *+91 7838100659 [IM-Gtalk]* > > > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *dguillett1 > *Sent:* 19 May 2012 6:52 > *To:* excel-macros@googlegroups.com > *Subject:* Re: $$Excel-Macros$$ Convert amount to Million > > > > ?? > > > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > > > *From:* Aamir Shahzad <aamirshahza...@gmail.com> > > *Sent:* Saturday, May 19, 2012 7:52 AM > > *To:* excel-macros@googlegroups.com > > *Subject:* $$Excel-Macros$$ Convert amount to Million > > > > Dear Group, > > > > Following macro is working fine but when run this code, Insert of column > process is too much slow, please see & amend. > > > > Private Sub Worksheet_Change(ByVal Target As Range) > > > Dim rngCell As Range > > For Each rngCell In Target > If Right(UCase(Trim(rngCell.Text)), 1) = "M" Then > rngCell.Value = Replace(UCase(Trim(rngCell.Text)), "M", "") * 10 ^ 6 > End If > Next > > End Sub > > -- > > Regards, > > Aamir Shahzad > > > -- > 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 > > -- > 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 > > -- > 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 > > > > > -- > > Regards, > > Aamir Shahzad > > > > -- > 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 > -- > 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 > -- Regards, Aamir Shahzad -- 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
