Simple Code but slow

[musa.biralo]

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

 

[RB Smissaert]

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]

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

 

[Don Guillett]

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

 

[RB Smissaert]

OK, try this:

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


RBS

 

[Dave Peterson]

Another way:

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

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

 

[dan dungan]

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 procedure:

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

 

[MP]

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

 

[RB Smissaert]

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

 

[Don Guillett]

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
========

 

[musa.biralo]

Dan,
You are the man!

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

 

[RB Smissaert]

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

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

 

[musa.biralo]

Thanks everybody for your great support!

musa.biralo

 

[dan dungan]

Thanks Don,

I'll look at this in the morning.

Dan

 

[Dave Peterson]

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.

 

[Gary Keramidas]

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