Roundup Nesting

[Guest]

John F wrote:
Help !! Another novice.
Excel 2003

Looking to nest 3 roundup statements.

While in cell K?, look to Cell J? ( one cell to left)
If J? <= 10.00, Roundup (J?,1)
If J? > 10.00 and <= 1,000.00, Roundup (J?,0)
If J? > 1,000.00, Roundup (J?,-1)

To make it more interesting, the nested instructions
should then "loop", to operate in each "K?" cell,
to end of file.

How would it define what is the end of the file?, as
there are a number of (sets of) blank cells that it
will have to "process" within the coloumn.

I hope to set up the final macro on a button
I just did one for a footer macro I made.

I hope this is enough detail for someone to help me out.
Thanks in advance.

John F. Scholten

 

[Frank Kabel]

Hi
maybe without a macro. Use the following formula in K1
=ROUNDUP(J1,1-(J1>10)-(J1>1000))
and copy this down for all rows

 

[Bob Phillips]

John,

Here is a shot

Dim cRows As Long
Dim i As Long

cRows = Cells(Rows.Count, "K").End(xlUp).Row
For i = 1 To cRows
If Cells(i, "J").Value <= 10 Then
Cells(i, "J").Valu = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 1)
ElseIf Cells(i, "J").Value <= 1000 Then
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, 0)
Else
Cells(i, "J").Value = WorksheetFunction.RoundUp(Cells(i,
"J").Value, -1)
End If
Next i

 

[Guest]

Frank, you're a genius.
Plain and simple is the way I like it.
Unfortunately, I can't "read" it, but I will work away at that.

However, I did overlooked 2 things.
1: Negative numbers should follow the same type of rounding
2: If NO numbers in certain cells in column J, can the result in K
Show as blank?

 

[Frank Kabel]

Hi
try:
=IF(J1<>"",ROUNDUP(J1,1-(ABS(J1)>10)-(ABS(J1)>1000)),"")

 

[Bob Phillips]

Frank,

I tried that, but is it what is wanted. It changes a negative number to a
positive, whereas (I think) the OP wants it to stay negative but follow the
rounding rules?

Bob

 

[Frank Kabel]

Hi Bob
for me it leaves a negative number unchanged? e.g.
-1.234
results in
-1.3

 

[Bob Phillips]

oops, sorry my mistake, I didn't spot you corrected my pathetic effort.

Bob

 

[Frank Kabel]

Hi Bob
you know while you prefer VBA I prefer (complicated) formulas <vbg>

 

[Guest]

Frank:
Bob is correct. I need the numbers to stay either positive or negative,
but follow the rounding rules regardless.
Blanks should show as blanks, NOT "0"'s, if possible.

Sorry I'm not making myself clear enough.

Thanks.

 

[Guest]

Bob:
I'm trying Franks idea for now.
Since I'm new at this, I'm trying the path of last resistance.
I'm too easily intimidated by VBA, at this time.
I've printing your answer off, to keep it on file.
Thanks

 

[Frank Kabel]

Hi
try the formula

 

[Bob Phillips]

I just thought that as you posted to .programming you wanted a VBA solution.
Personally I would use the formula too<g>

Bob

 

[Guest]

Hi, again.
Works like a dream - Tested all the combinations I could think of,
Including positives and negatives.
Is now in use in my spread sheet.
Thanks again.