Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Simple Code but slow
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 411
Default 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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 19
Default 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


Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default 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



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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

Reply With QuoteReply With Quote
  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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

Reply With QuoteReply With Quote
  #12   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,452
Default 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



Reply With QuoteReply With Quote
  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 55
Default 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

Reply With QuoteReply With Quote
  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 411
Default Simple Code but slow
Thanks Don,

I'll look at this in the morning.

Dan
Reply With QuoteReply With Quote
  #15   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow code when used as VBA code instead of macro (copying visible columns) [email protected] Excel Programming 3 April 2nd 07 05:26 PM
Can someone help me with this slow code BillReese Excel Programming 7 September 3rd 05 12:34 AM
Simple worksheet becomes large and slow Judy Excel Discussion (Misc queries) 3 May 10th 05 10:52 PM
SLOW Code... Ernst Guckel[_4_] Excel Programming 2 March 20th 05 10:58 AM
Is this slow code? Tom Excel Programming 4 March 3rd 04 11:18 PM


All times are GMT +1. The time now is 01:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement