Simple Code but slow
 

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

Simple Code but slow
 

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

Simple Code but slow
 

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

Simple Code but slow
 

May be quicker??

Sub counterif()
Set ms = Selection
For i = 2 To ms.Rows.Count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(ms.Rows.Count)))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

Simple Code but slow
 

OK, try this:

DataCell = "MUSA_" & String(5 - Len(CStr(i)), "0") & i


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

Simple Code but slow
 

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

Simple Code but slow
 

Hi Don,

I'm testing this to better understand how vba works.

When I run this procedure it returns "Variable not defined".

So I added to the procedu

Sub counterif()
Dim ms As Range
Set ms = Selection
Dim i As Integer

For i = 2 To ms.Rows.Count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(ms.Rows.Count)))
Next i
End Sub

Now when I run it the procedure returns:
Compile error:
Variable required--can't assign to this expression

Format(i, Application.Rept("0", Len(ms.Rows.Count)))

..Count is highlighted.

Dan

Simple Code but slow
 

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

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

Simple Code but slow
 

Try it this way
=======
Option Explicit

Sub counterif()
Dim ms As Range
Dim i As Long
Dim count As Long
Dim x As Integer

Set ms = Selection
x = ms.Rows.count
For i = 2 To ms.Rows.count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(x)))
Next i
End Sub
========
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"dan dungan" wrote in message
...
Hi Don,

I'm testing this to better understand how vba works.

When I run this procedure it returns "Variable not defined".

So I added to the procedu

Sub counterif()
Dim ms As Range
Set ms = Selection
Dim i As Integer

For i = 2 To ms.Rows.Count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(ms.Rows.Count)))
Next i
End Sub

Now when I run it the procedure returns:
Compile error:
Variable required--can't assign to this expression

Format(i, Application.Rept("0", Len(ms.Rows.Count)))

.Count is highlighted.

Dan

Simple Code but slow
 

Dan,
You are the man!

It did reduce some time but nothing significantly....that's fine...
Thanks again!

On Jan 16, 6:36 pm, "Don Guillett" wrote:
May be quicker??

Sub counterif()
Set ms = Selection
For i = 2 To ms.Rows.Count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(ms.Rows.Count)))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
" 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

Simple Code but slow
 

Yes, that probably is faster:

Sub test3()

Dim i As Long
Dim n As Long
Dim DataCell As Range
Dim strZeros As String
Dim arr

If MsgBox("Did you select the range of cells to insert the IDM ID?", _
vbYesNo, _
"Select required range?") = vbNo Then
Exit Sub
End If

arr = Selection

n = 5
strZeros = String(n, "0")

For i = 1 To UBound(arr)
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
arr(i, 1) = "MUSA_" & strZeros & i
Next i

Selection = arr

End Sub


RBS


"MP" wrote in message
...
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

Simple Code but slow
 

Thanks everybody for your great support!

musa.biralo


On Jan 16, 7:01 pm, Dave Peterson wrote:
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

Simple Code but slow
 

Thanks Don,

I'll look at this in the morning.

Dan

Simple Code but slow
 

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

Simple Code but slow
 

you don't say how big the range is, did you try turning off screenupdating and
setting calculation to manual?

--


Gary


"musa.biralo" wrote in message
...
Dan,
You are the man!

It did reduce some time but nothing significantly....that's fine...
Thanks again!

On Jan 16, 6:36 pm, "Don Guillett" wrote:
May be quicker??

Sub counterif()
Set ms = Selection
For i = 2 To ms.Rows.Count
Cells(i, Selection.Column) = "MUSA_" & _
Format(i, Application.Rept("0", Len(ms.Rows.Count)))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
" 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