Roundup Nesting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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:
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.
 
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
 
I just thought that as you posted to .programming you wanted a VBA solution.
Personally I would use the formula too<g>

Bob
 
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.
 
Back
Top