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 <tel:%2B91%207838100659> [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 <mailto: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
