manual adjustments
 

Hi there,

This probably is really simple but basically what I want to do is b
able to quickly search a spreadsheet to see if people have made manua
adjustments.

Basically, we often download stuff from other databases into excel, s
most of the cells will be populated with stuff like:

=kfcell(B3,B4,B5.....B8)

one complication is that since these numbers are often huge, they ar
usually divided by either 1000000 or 1000000000.

what I want to do is find all cells that have + or - adjustments i
them, for example, find:

=kfcell(B3,B4,B5.....B8)/1000000+3.1

or

=kfcell(B3,B4,B5.....B8)/1000000000-13.4

but ignore

=kfcell(B3,B4,B5.....B8)/1000000

I am also still not sure about the best way to return those values.
think that, ideally, I would like there to be an option box for th
user to choose whether to have a list of affected cells or to highligh
the affected cells or both.

I figure it's probably going to have to be a macro but I have no ide
how to use forms.

Can anyone hel

--
Message posted from http://www.ExcelForum.com

manual adjustments
 

T,

The macro below will highlight, in red, any formula with a + or - sign in
it, and put the cell addresses in column A, starting below the last filled
cell.

--
HTH,
Bernie
MS Excel MVP

Sub FindManuallyAdjustedFormulas()
' Macro written by Bernie Deitrick

Dim myCell As Range
For Each myCell In Cells.SpecialCells(xlCellTypeFormulas, 23)
If InStr(1, myCell.Formula, "+") 0 Or _
InStr(1, myCell.Formula, "-") 0 Then
myCell.Interior.ColorIndex = 3
Range("A65536").End(xlUp)(2).Value = myCell.Address
Else
myCell.Interior.ColorIndex = xlNone
End If
Next myCell

End Sub


"tmischler " wrote in message
...
Hi there,

This probably is really simple but basically what I want to do is be
able to quickly search a spreadsheet to see if people have made manual
adjustments.

Basically, we often download stuff from other databases into excel, so
most of the cells will be populated with stuff like:

=kfcell(B3,B4,B5.....B8)

one complication is that since these numbers are often huge, they are
usually divided by either 1000000 or 1000000000.

what I want to do is find all cells that have + or - adjustments in
them, for example, find:

=kfcell(B3,B4,B5.....B8)/1000000+3.1

or

=kfcell(B3,B4,B5.....B8)/1000000000-13.4

but ignore

=kfcell(B3,B4,B5.....B8)/1000000

I am also still not sure about the best way to return those values. I
think that, ideally, I would like there to be an option box for the
user to choose whether to have a list of affected cells or to highlight
the affected cells or both.

I figure it's probably going to have to be a macro but I have no idea
how to use forms.

Can anyone help


---
Message posted from http://www.ExcelForum.com/