Macro for finding values

Posted by Chris Rock on September 10, 2001 2:15 PM

I've got a sheet with values on it, formatted to show no decimal places.

I'd like to write a macro that can look at my data, find the cells containing values less than 1, and applying a ROUNDUP formula.

The values are referenced from another sheet, so the formula would look like this: =ROUNDUP('SheetName'!A1,0).

Can someone help me? I'm not sure how the code is written, but I think the logical steps are these:

Start in Column A.
Look at cell value.
Determine if value is less than 1.
If it is, apply formula.
If it is not, continue down to next cell.
Repeat.

When end of data in Column A is reached, continue to Column B - repeating to Column F.

If there's an easier way to do this, I'd not mind being shown.

Thanks in advance if anyone can help.

PS - if there's a way to simply select all the cells with values &LT; 1, then add that formula to the all of those cells at once, I guess that would be easiest.

Posted by Barrie Davidson on September 10, 2001 2:33 PM

Chris, could you use an IF statement and copy the formula? The reason I ask is that, if you choose a VBA loop, it could take a while for Excel to loop through all the cells.

Regards,
BarrieBarrie Davidson

Posted by Chris Rock on September 10, 2001 2:42 PM

Now that I think of it, that would be easiest.

Thanks for the idea. After staring at a spreadsheet all day, it's easy to forget the basics.

Thanks again.

Posted by Barrie Davidson on September 10, 2001 2:56 PM

I know what you mean

When this happens to me I refer to it as "The forest for the trees syndrome". As in, I can't see the forest for the trees.

Barrie :)
Barrie Davidson

Posted by Tom Urtis on September 10, 2001 3:10 PM

Hey Barrie, nice web site

I'm creating a web site now too, dropped in to see your's, nice job.

Tom U

: Now that I think of it, that would be easiest.

Posted by Henry Root on September 10, 2001 4:43 PM

Not sure how to interpret your formula(relative? absolute?), but here's a macro to select all cells less tha 1. You can then either manually enter the required formula(Ctrl+Enter) or add a line to the macro to enter it(use the macro recorder to find out the code).

Sub SelectCells()
Dim rng As Range, cell As Range, chg As Range
Set rng = Intersect(ActiveSheet.UsedRange, Columns("A:F"))
For Each cell In rng
If cell.Value &LT; 1 Then
If Not chg Is Nothing Then
Set chg = Union(chg, cell)
Else
Set chg = cell
End If
End If
Next
chg.Select
End Sub

Posted by Barrie Davidson on September 11, 2001 6:36 AM

Thanks Tom

When you're finished with your site I'd like to have a look at it. Would you send me a link to it?

Barrie :