Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
Something along these lines
Dim x as Integer For x = 1 to 100 Cells(x,1) = x/100 Next -- steveB Remove "AYN" from email to respond "MichaelC" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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 "MichaelC" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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. "STEVE BELL" wrote: Something along these lines Dim x as Integer For x = 1 to 100 Cells(x,1) = x/100 Next -- steveB Remove "AYN" from email to respond "MichaelC" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
Jim, Steve - you guys are great. Thanks so much. Problem solved
"Jim Cone" wrote: 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 "MichaelC" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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 : 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 "MichaelC" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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 "keepITcool" wrote in message ft.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Make a Loop count by 1% not 1
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 : 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 "keepITcool" wrote in message ft.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count rownumbers in loop | Excel Discussion (Misc queries) | |||
loop count | Excel Discussion (Misc queries) | |||
Jump to the next count in a FOR Loop | Excel Programming | |||
Help with macro to make it loop through coloums | Excel Programming |