Using arrays may be quicker, but it sounds like it could take a lot of coding
time to get it perfekt <vbg.

I'm guessing that the OP wanted a bunch of those values in a single column (of a
single area). If that were the case, I'd write a formula to the selected range
once and then convert it to values.

Option Explicit
Sub testme02()

Dim TotalRows As Long
Dim myNumberFormat As String
Dim StartRow As Long
Dim Response2Msg As Long

Response2Msg = MsgBox("Did you select the range of" _
& " cells to insert the IDM ID?", vbYesNo, "Select required range?")

If Response2Msg = vbNo Then
Exit Sub
End If

With Selection.Areas(1).Columns(1)
TotalRows = .Cells.Count
StartRow = .Row
myNumberFormat = String(Len(Format(TotalRows, "0")), "0")
.Formula = "=""MUSA_""" & "&text(row()-" & StartRow & "+1," _
& """" & myNumberFormat & """)"

.Copy
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

'or
'.Value = .Value
End With
End Sub

==
And if you look at the OP's code, you'll notice that he does the same thing each
pass through the "for/next" loop.

The number of rows in the selection won't change, so that could be moved before
the loop.

The numberformat doesn't change, so that could be move before the loop.

This still takes a pretty good time in xl2003 with a single column (65536 cells)
selected:

Option Explicit
Sub testme()

Dim DataCell As Range
Dim TotalData As Long
Dim myNumberFormat As String
Dim Response2Msg As Long
Dim i As Long

Response2Msg = MsgBox("Did you select the range of" _
& " cells to insert the IDM ID?", vbYesNo, "Select required range?")

If Response2Msg = vbNo Then
Exit Sub
End If

TotalData = Selection.Rows.Count

If TotalData < 10 Then
myNumberFormat = "0"
ElseIf TotalData = 10 And TotalData < 100 Then
myNumberFormat = "00"
ElseIf TotalData = 100 And TotalData < 1000 Then
myNumberFormat = "000"
ElseIf TotalData = 1000 And TotalData < 10000 Then
myNumberFormat = "0000"
ElseIf TotalData = 10000 And TotalData < 100000 Then
myNumberFormat = "00000"
End If

i = 1
For Each DataCell In Selection.Cells
DataCell.Value = "MUSA_" & Format(i, myNumberFormat)
i = i + 1
Next DataCell

End Sub

I also dimmed my numbers as Long, not integer. Integers don't go high enough
for all the rows in xl2003.

And I changed the "else End" to "exit sub" to exit the sub nicely.

MP wrote:

Dave,
Isn't the reason his code is 'slow' is due to writing cell by cell.
I wouldn't think it was the format slowing it down, - whatever the correct
syntax for his format statement(his op shows multiple format requirements
depending on value of cell)
I thought he could read the range in to an array, format the array (using a
second array if need be) as req'd, then write back to the req'd range?
I don't know excel enough to know the syntax without looking it up but is
that even a possible concept?
Mark

I thought there was a way to write a range from an array in one call
"Dave Peterson" wrote in message
...
Another way:

DataCell = "MUSA_" & format(i,"000")

Kind of like =text() in a worksheet cell.

"musa.biralo" wrote:

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

--

Dave Peterson

--

Dave Peterson