Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot adjustments | Excel Discussion (Misc queries) | |||
Row Height Adjustments | Excel Discussion (Misc queries) | |||
time adjustments | Excel Discussion (Misc queries) | |||
Adjustments | New Users to Excel | |||
Help! need some adjustments! | Excel Programming |