Simple Code but slow
Something like this will probably be faster:
Sub test2()
Dim i As Long
Dim n As Long
Dim DataCell As Range
Dim strZeros As String
If MsgBox("Did you select the range of cells to insert the IDM ID?", _
vbYesNo, _
"Select required range?") = vbNo Then
Exit Sub
End If
n = 5
strZeros = String(n, "0")
For Each DataCell In Selection
i = i + 1
If i = 10 Or i = 100 Or i = 1000 Or i = 10000 Or i = 100000 Then
n = n - 1
strZeros = String(n, "0")
End If
DataCell = "MUSA_" & strZeros & i
Next DataCell
End Sub
RBS
"musa.biralo" wrote in message
...
Thanks RBS!
well, I don't simply want to use MUSA_1, MUSA_2 IDs. I want MUSA_001,
MUSA_002 so i have to use some format thing....I don't think your code
will result what i am expecting....
Thanks though.
musa.biralo
On Jan 16, 5:48 pm, "RB Smissaert"
wrote:
This should do the same and faster:
Sub test()
Dim DataCell As Range
Dim i As Long
If MsgBox("Did you select the range of cells to insert the IDM ID?", _
vbYesNo, _
"Select required range?") = vbNo Then
Exit Sub 'best not to use End
End If
For Each DataCell In Selection
i = i + 1
DataCell = "MUSA_" & i
Next DataCell
End Sub
RBS
"musa.biralo" wrote in message
...
Hi there,
The code below takes a bit time to populate the ID if I select bigger
range....Is there any way to expedite it.
Any help will be appreciated!!!!
Dim DataCell As Range
Dim TotalData As Integer
Dim Counter
Response2Msg = MsgBox("Did you select the range of cells to insert the
IDM ID?", vbYesNo, "Select required range?")
If Response2Msg = vbNo Then End
i = 1
For Each DataCell In Selection
TotalData = Selection.Rows.Count
If TotalData < 10 Then
Counter = Format(i, "0")
ElseIf TotalData = 10 And TotalData < 100 Then
Counter = Format(i, "00")
ElseIf TotalData = 100 And TotalData < 1000 Then
Counter = Format(i, "000")
ElseIf TotalData = 1000 And TotalData < 10000 Then
Counter = Format(i, "0000")
ElseIf TotalData = 10000 And TotalData < 100000 Then
Counter = Format(i, "00000")
End If
DataCell.Value = "MUSA_" & Counter
i = i + 1
Next DataCell
|