How to Make a Loop count by 1% not 1

[MichaelC]

I have a macro that performs a looped calculation stepping up the input value
by 1 for each count.
I want to use the same macro to perform the same looped calculation starting
at 1% and ending at 100%, but have been unsuccessful.
My power Programming book is silent on this. I would much appreciate any
help.
Here is my macro:
Sub GoodLoop()
StartVal = 1
NumToFill = Sheets("Control").Range("E17")
ActiveCell.Value = StartVal
For Cnt = 0 To NumToFill - 1
ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
Next Cnt

End Sub

 

[STEVE BELL]

Something along these lines

Dim x as Integer

For x = 1 to 100
Cells(x,1) = x/100
Next

 

[Jim Cone]

or
'-----------------------
Sub GoodLoop()
Dim Cnt As Double
Dim StartVal As Double
Dim NumToFill As Double
Dim dblIncrement As Double

NumToFill = Sheets("Control").Range("E17")
dblIncrement = 1 / NumToFill
StartVal = dblIncrement

For Cnt = 0 To NumToFill - 1
ActiveCell.Offset(Cnt, 0).Value = Format(StartVal, "0.00%")
StartVal = StartVal + dblIncrement
Next Cnt

End Sub
'---------------------------------

Jim Cone
San Francisco, USA




I have a macro that performs a looped calculation stepping up the input value
by 1 for each count.
I want to use the same macro to perform the same looped calculation starting
at 1% and ending at 100%, but have been unsuccessful.
My power Programming book is silent on this. I would much appreciate any
help.
Here is my macro:
Sub GoodLoop()
StartVal = 1
NumToFill = Sheets("Control").Range("E17")
ActiveCell.Value = StartVal
For Cnt = 0 To NumToFill - 1
ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt
Next Cnt

End Sub

 

[MichaelC]

Thanks Steve - I am not quite with you yet.
If your "x" is equivalent to my Cnt
Then I don't understand the Cells(x,1) = x/100 part
I don' know see what that expression will do.

 

[MichaelC]

Jim, Steve - you guys are great. Thanks so much. Problem solved

 

[keepITcool]

Jim,

note that writing Format(1.23,"%") it will not
work in situations where users have a comma as decimal separator.

I think that a better way to do this would be:

Sub AlsoLoop()
Dim Cnt As Long
Dim NumToFill As Long

NumToFill = Worksheets(1).Range("a1")

ActiveCell.Resize(NumToFill).NumberFormat = "0.00%"
For Cnt = 1 To NumToFill
ActiveCell.Cells(Cnt, 1).Value = Application.Round(Cnt / NumToFill, 4)
Next Cnt

End Sub






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Cone wrote :

 

[Jim Cone]

Thank you for pointing that out, I will keep it for future reference.

Would I assume correctly that: .Value = Format$(StartVal, "Percent")
would not create a problem?

For others reading this, it should be noted that formatting and
calculation should not be done inside a loop, if it can be avoided.

Regards,
Jim Cone
San Francisco, USA


Jim,
note that writing Format(1.23,"%") it will not
work in situations where users have a comma as decimal separator.
I think that a better way to do this would be:

Sub AlsoLoop()
Dim Cnt As Long
Dim NumToFill As Long
NumToFill = Worksheets(1).Range("a1")
ActiveCell.Resize(NumToFill).NumberFormat = "0.00%"
For Cnt = 1 To NumToFill
ActiveCell.Cells(Cnt, 1).Value = Application.Round(Cnt / NumToFill, 4)
Next Cnt
End Sub

 

[keepITcool]

wrong assumption.

Be carefull to assign a localized string to the value property.
goes for dates and decimal numbers... you're asking for trouble

Format is internationally aware...thus Format(1.2345,"Percent")
produces a string "123,45%" on a system with a comma as decimal
separator.

It's inserted as a string. LEFT aligned. You COULD use it in formulas
because excel will try to evaluate cell references used in formulas as
numbers... but it's shaky.. (it will only be converted to a VALUE(
number) when the user presses F2 enter... if you leave it a string
then opening the sheet in a different locale spells trouble.

It makes far more sense to assign a Double to .Value
THEN format it using numberformat.

When dates can be involved it may get worse so for
safety I often just use .Value2 (and .Numberformat)

If you want to experiment, just play around with Regional Settings.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Cone wrote :

 

[Tushar Mehta]

The OP is probably no longer interested, but this is vulnerable to
rounding errors. Use
ActiveCell.Offset(Cnt, 0).Value = StartVal + Cnt * Increment
'StartVal = StartVal + dblIncrement

Also, why put the values as text? Just set the entire range's
NumberFormat propery?

ActiveCell.resize(numtofill-1,1).numberformat="0.00%"

[None of the code above has been tested.]



--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions