Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find, cells with spaces
Hi All,
I'm building the sub below to look for values, or to see if a range is devoid of data. The wrinkle is that at times, cells containing ONLY spaces are deemed to be devoid of data. In testing, so far it seems to work, but since I'm a beginner with special cells, I'd like to know if there's a better way to make that part of the sub more efficient. In the app I'm building, this sub will be used a lot. The ranges will typically vary between 1 row and lots of rows in a ws, columns from 1 to about 90. Thanks much, Neal Sub Find_Valu(Ws As Worksheet, sLookFor As String, _ FoundRow As Long, FoundCol As Integer, _ bXlWhole As Boolean, _ FmRow As Long, FmCol As Integer, _ Optional RngToRow As Long = 0, Optional RngToCol As Integer = 0, _ Optional bTestForHidden As Boolean = False, _ Optional bIgnoreSpaces As Boolean = False) ' Return the row and col of the 1st cell where a value is found. ' Zeros returned if not found. bXlwhole = true = entire cell. ' If MORE THAN ONE CELL is to be searched, RngToRow AND RngToCol parms must be not zero. ' IF ANY cell being searched MIGHT be hidden, bTestForHidden must be True. Dim SearchRng As Range Dim Arg As Range, OneCell As Range Dim WhoOrPrt Dim Row As Long, Col As Integer Dim HideId As String Dim bAnyHidden As Boolean If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart If RngToRow = 0 Or RngToCol = 0 Then RngToRow = FmRow RngToCol = FmCol End If FoundRow = 0 FoundCol = 0 If bTestForHidden = True Then Call Find_Hidden(Ws, FmRow, FmCol, RngToRow, RngToCol, HideId, 0) If HideId < "" Then bAnyHidden = True End If Set SearchRng = Ws.Range(Ws.Cells(FmRow, FmCol), Ws.Cells(RngToRow, RngToCol)) If bAnyHidden = False Then Set Arg = SearchRng.Find(sLookFor, After:=Ws.Cells(RngToRow, RngToCol), _ LookIn:=xlValues, Lookat:=WhoOrPrt) If Arg Is Nothing Then Exit Sub 'note; RmAnyValue is a public constant = "*" If sLookFor = RmAnyValue And bIgnoreSpaces = True Then Set Arg = SearchRng.SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers) For Each OneCell In Arg.Cells If Trim(OneCell.Value) < "" Then ''OneCell.Select 'test ''MsgBox "Value:" & OneCell.Value & Dash, , OneCell.Address 'test FoundRow = OneCell.Row FoundCol = OneCell.Column Exit Sub End If Next OneCell Exit Sub End If ''Arg.Select 'test ''MsgBox "Value:" & Arg.Value & Dash, , Arg.Address 'test FoundRow = Arg.Row FoundCol = Arg.Column Exit Sub Else 'f .Find fails with hidden stuff. For Row = FmRow To RngToRow For Col = FmCol To RngToCol If sLookFor < RmAnyValue Then 'f any value is "*" If WhoOrPrt = xlWhole Then If Ws.Cells(Row, Col).Value = sLookFor Then FoundRow = Row FoundCol = Col Exit Sub End If Else If InStr(Ws.Cells(Row, Col).Value, sLookFor) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If Else 'f for any value, check length If bIgnoreSpaces = False Then If Ws.Cells(Row, Col).Value < "" Then FoundRow = Row FoundCol = Col Exit Sub End If Else 'f for any value, ignore only space(s) If Len(Trim(Ws.Cells(Row, Col).Value)) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If End If Next Col Next Row End If End Sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find, cells with spaces
Try testing for length:
if Len(activecell)0 Then do stuff Else End if "Neal Zimm" wrote: Hi All, I'm building the sub below to look for values, or to see if a range is devoid of data. The wrinkle is that at times, cells containing ONLY spaces are deemed to be devoid of data. In testing, so far it seems to work, but since I'm a beginner with special cells, I'd like to know if there's a better way to make that part of the sub more efficient. In the app I'm building, this sub will be used a lot. The ranges will typically vary between 1 row and lots of rows in a ws, columns from 1 to about 90. Thanks much, Neal Sub Find_Valu(Ws As Worksheet, sLookFor As String, _ FoundRow As Long, FoundCol As Integer, _ bXlWhole As Boolean, _ FmRow As Long, FmCol As Integer, _ Optional RngToRow As Long = 0, Optional RngToCol As Integer = 0, _ Optional bTestForHidden As Boolean = False, _ Optional bIgnoreSpaces As Boolean = False) ' Return the row and col of the 1st cell where a value is found. ' Zeros returned if not found. bXlwhole = true = entire cell. ' If MORE THAN ONE CELL is to be searched, RngToRow AND RngToCol parms must be not zero. ' IF ANY cell being searched MIGHT be hidden, bTestForHidden must be True. Dim SearchRng As Range Dim Arg As Range, OneCell As Range Dim WhoOrPrt Dim Row As Long, Col As Integer Dim HideId As String Dim bAnyHidden As Boolean If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart If RngToRow = 0 Or RngToCol = 0 Then RngToRow = FmRow RngToCol = FmCol End If FoundRow = 0 FoundCol = 0 If bTestForHidden = True Then Call Find_Hidden(Ws, FmRow, FmCol, RngToRow, RngToCol, HideId, 0) If HideId < "" Then bAnyHidden = True End If Set SearchRng = Ws.Range(Ws.Cells(FmRow, FmCol), Ws.Cells(RngToRow, RngToCol)) If bAnyHidden = False Then Set Arg = SearchRng.Find(sLookFor, After:=Ws.Cells(RngToRow, RngToCol), _ LookIn:=xlValues, Lookat:=WhoOrPrt) If Arg Is Nothing Then Exit Sub 'note; RmAnyValue is a public constant = "*" If sLookFor = RmAnyValue And bIgnoreSpaces = True Then Set Arg = SearchRng.SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers) For Each OneCell In Arg.Cells If Trim(OneCell.Value) < "" Then ''OneCell.Select 'test ''MsgBox "Value:" & OneCell.Value & Dash, , OneCell.Address 'test FoundRow = OneCell.Row FoundCol = OneCell.Column Exit Sub End If Next OneCell Exit Sub End If ''Arg.Select 'test ''MsgBox "Value:" & Arg.Value & Dash, , Arg.Address 'test FoundRow = Arg.Row FoundCol = Arg.Column Exit Sub Else 'f .Find fails with hidden stuff. For Row = FmRow To RngToRow For Col = FmCol To RngToCol If sLookFor < RmAnyValue Then 'f any value is "*" If WhoOrPrt = xlWhole Then If Ws.Cells(Row, Col).Value = sLookFor Then FoundRow = Row FoundCol = Col Exit Sub End If Else If InStr(Ws.Cells(Row, Col).Value, sLookFor) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If Else 'f for any value, check length If bIgnoreSpaces = False Then If Ws.Cells(Row, Col).Value < "" Then FoundRow = Row FoundCol = Col Exit Sub End If Else 'f for any value, ignore only space(s) If Len(Trim(Ws.Cells(Row, Col).Value)) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If End If Next Col Next Row End If End Sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find, cells with spaces
Thanks jr, it's what i came up with too.
i think you have to trim the cell 1st, tho' to elim the blanks, then check len. -- Neal Z "JRForm" wrote: Try testing for length: if Len(activecell)0 Then do stuff Else End if "Neal Zimm" wrote: Hi All, I'm building the sub below to look for values, or to see if a range is devoid of data. The wrinkle is that at times, cells containing ONLY spaces are deemed to be devoid of data. In testing, so far it seems to work, but since I'm a beginner with special cells, I'd like to know if there's a better way to make that part of the sub more efficient. In the app I'm building, this sub will be used a lot. The ranges will typically vary between 1 row and lots of rows in a ws, columns from 1 to about 90. Thanks much, Neal Sub Find_Valu(Ws As Worksheet, sLookFor As String, _ FoundRow As Long, FoundCol As Integer, _ bXlWhole As Boolean, _ FmRow As Long, FmCol As Integer, _ Optional RngToRow As Long = 0, Optional RngToCol As Integer = 0, _ Optional bTestForHidden As Boolean = False, _ Optional bIgnoreSpaces As Boolean = False) ' Return the row and col of the 1st cell where a value is found. ' Zeros returned if not found. bXlwhole = true = entire cell. ' If MORE THAN ONE CELL is to be searched, RngToRow AND RngToCol parms must be not zero. ' IF ANY cell being searched MIGHT be hidden, bTestForHidden must be True. Dim SearchRng As Range Dim Arg As Range, OneCell As Range Dim WhoOrPrt Dim Row As Long, Col As Integer Dim HideId As String Dim bAnyHidden As Boolean If bXlWhole = True Then WhoOrPrt = xlWhole Else WhoOrPrt = xlPart If RngToRow = 0 Or RngToCol = 0 Then RngToRow = FmRow RngToCol = FmCol End If FoundRow = 0 FoundCol = 0 If bTestForHidden = True Then Call Find_Hidden(Ws, FmRow, FmCol, RngToRow, RngToCol, HideId, 0) If HideId < "" Then bAnyHidden = True End If Set SearchRng = Ws.Range(Ws.Cells(FmRow, FmCol), Ws.Cells(RngToRow, RngToCol)) If bAnyHidden = False Then Set Arg = SearchRng.Find(sLookFor, After:=Ws.Cells(RngToRow, RngToCol), _ LookIn:=xlValues, Lookat:=WhoOrPrt) If Arg Is Nothing Then Exit Sub 'note; RmAnyValue is a public constant = "*" If sLookFor = RmAnyValue And bIgnoreSpaces = True Then Set Arg = SearchRng.SpecialCells(xlCellTypeConstants, xlTextValues + xlNumbers) For Each OneCell In Arg.Cells If Trim(OneCell.Value) < "" Then ''OneCell.Select 'test ''MsgBox "Value:" & OneCell.Value & Dash, , OneCell.Address 'test FoundRow = OneCell.Row FoundCol = OneCell.Column Exit Sub End If Next OneCell Exit Sub End If ''Arg.Select 'test ''MsgBox "Value:" & Arg.Value & Dash, , Arg.Address 'test FoundRow = Arg.Row FoundCol = Arg.Column Exit Sub Else 'f .Find fails with hidden stuff. For Row = FmRow To RngToRow For Col = FmCol To RngToCol If sLookFor < RmAnyValue Then 'f any value is "*" If WhoOrPrt = xlWhole Then If Ws.Cells(Row, Col).Value = sLookFor Then FoundRow = Row FoundCol = Col Exit Sub End If Else If InStr(Ws.Cells(Row, Col).Value, sLookFor) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If Else 'f for any value, check length If bIgnoreSpaces = False Then If Ws.Cells(Row, Col).Value < "" Then FoundRow = Row FoundCol = Col Exit Sub End If Else 'f for any value, ignore only space(s) If Len(Trim(Ws.Cells(Row, Col).Value)) 0 Then FoundRow = Row FoundCol = Col Exit Sub End If End If End If Next Col Next Row End If End Sub -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging cells and eliminating spaces for empty cells | Excel Discussion (Misc queries) | |||
how can I find cell/s in excel sheet having blank spaces more tha. | Excel Discussion (Misc queries) | |||
FIND TAB, Replace with Spaces (3) or ... | Excel Discussion (Misc queries) | |||
Using FormulaR1C1 to find fields begining with spaces | Excel Programming | |||
Find number of spaces in a string (instr) | Excel Programming |