| Home |
| Search |
| Today's Posts |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find not equal to "0"
Errrrr. This is looping through the cells.
Mark wrote: Many thanks Joerg I'll give it a go Mark "Joerg" wrote in message ... Well, I guess there are ways in Excel to find something not equal to in Excel. The following macro selects the first non-zero value in each row of a selected range. If just a single cell is selected, the macro will select such non-zero values for the whole worksheet. Cheers, Joerg Mochikun Sub FindFirstNonZeroInEachRow() Dim rng, MyArea As Range Dim RowNumber If Selection.Cells.Count = 1 Then Set rng = ActiveSheet.UsedRange Else Set rng = Intersect(ActiveSheet.UsedRange, Selection) End If For Each cell In rng If cell.Value < 0 And RowNumber < cell.Row Then If MyArea Is Nothing Then Set MyArea = cell Set MyArea = Union(MyArea, cell) RowNumber = cell.Row End If Next cell If MyArea Is Nothing Then MsgBox "No non-zero values found!" Else MyArea.Select End If End Sub "Mark" wrote in message ... Hi I want to find the first cell that is not zero "0" across a row. I have tried the following but is gives a syntax error. (I've searched this forum but cannot find anything that helps me) Cells.Find(What:<"0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate Any help will be greatly appreciated. Mark -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "Find" a wildcard as a place marker and "replace" with original va | Excel Discussion (Misc queries) | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| Find "Not Equal" | Excel Programming | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Threaded Mode
