Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Hi -
I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
I modified the code the way microsoft would write the code. You didn't
specify lookin:=values and lookat:=xlwhole. I removed an unecessay IF statement in the loop and changed "Loop until" to "loop while", and added a NOT. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) Else Set Rng = .Find(what:=vFind, after:=AfterRng, LookIn:=xlvalues, LookAt:=xlvalues) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(after:=Rng) lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop while not Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub "Neal Zimm" wrote: Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Hi Joel, Thanks for the effort, I think.
This will be harsh, but I'm writing this paragraph after completing the rest of what you're about to read. This is the first time I've received advice on this community that's just bad and wrong. Sorry, I think you took very little time and care with your response. It seems you do not know very much about optional passing of argument values and your VBA syntax looked sloppy. You do not have to respond to this posting, but I would be happy to discuss any of it if you think it's wrong. I'm sorry I did not make it clearer in my posting what my real problem is. The proc DOES find strings very nicely. When vFind is loaded with a numeric value like 1300 it finds 1st occurence and dupes just fine. When vFind is loaded with a value like 1401.61, it's first cell is SOMETIMES NOT found and additional cells with this value are not found. I'm guessing it has something to do with the way the data is stored in the sheet, or the way I'm doing stuff like: vFind = Varname 'before the call. I DON'T think your changes have much to do with the above problem, AND, sorry to say, Many of them are Wrong or not needed. (I can't think of a way to "change" vFind inside the proc) . I have problems with some of your suggestions, but I'm still learning about ..find and .findnext, please comment on them so I can be sure. Thanks. "You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ. Proc argument: Optional bWhole As Boolean = True mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart Proc argument: Optional LookIn As Integer = xlValues Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values Call FindRngValues(.......,xlFormulas,,) I want the Sub to be able to find values OR formulas, (or constants in the cells) which are the numbers relating to my problem. "I modified the code the way microsoft would write the code." MSo provides examples, which I changed to meet MY functional needs, especially the ability to vary the meaning of the returned Found1Rng and DupeRng arguments. DupeRng either contains Found1Rng or not. See bOneRng argument. "I modified the code the way microsoft would write the code." My understanding of using named arguments is that you NEED them if you are leaving standard argument values out, or changing the left to right order of input. If AfterRng Is Nothing Then 'Why your change?? My ", ," leaves out the After argument, AND, you have ' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention, ' hence my use of the values in the LookIn and LookAt arguments for ' lookin values/formulas and lookat whole/part Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal Else 'Joel Set Rng = .Find(what:=vFind, after:=AfterRng, _ LookIn:=xlvalues,LookAt:=xlvalues) Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal 'Again, why your change ??? Since the first 4 values in .Find are what, after, 'lookin, lookat I saw no need for using named arguments, and you were 'careless here, you used xlValues for both LookIn: and LookAt: End If "I removed an unecessay IF statement in the loop." I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am not making these changes. As I said in my problem statement, this proc is working just fine when vFind contains a string value. If Not Rng Is Nothing Then '1st find was successful Set Found1Rng = Rng 'capture 1st range FirAdr = Found1Rng.Address 'capture address of 1st found range Do 'try to find 2nd and more Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the ONLY argument 'Joel REMOVED line below ' If Not Rng Is Nothing And Rng.Address < FirAdr Then ' Well, you've blasted to smithereens a key part of my logic. ' 1. The code below will run whether or NOT .FindNext found something. ' I want the cell count and DupeRng ONLY for .FindNext that results in a ' find which typically is not the 1st find. ' 2. Further, suppose .FindNext works once, and then finds nothing more. ' Rng will be nothing and the DupeRng union statement will blow up as it ' requires not nothing arguments. lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If 'Your ERROR, you removed the If, but NOT the end if. Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from Until End if Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice of style as well as substance. I understand the change to While because you took out the If statement. BUT "While" with " Or Rng.Address = FirAdr" is DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps around and finds the 1st value again. This change will cause a NEVER ENDING loop when .findnext finds at least a second occurence of the vFind value. If you can get your code to run, try it out. -- Neal Z "Joel" wrote: I modified the code the way microsoft would write the code. You didn't specify lookin:=values and lookat:=xlwhole. I removed an unecessay IF statement in the loop and changed "Loop until" to "loop while", and added a NOT. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) Else Set Rng = .Find(what:=vFind, after:=AfterRng, LookIn:=xlvalues, LookAt:=xlvalues) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(after:=Rng) lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop while not Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub "Neal Zimm" wrote: Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Hi Neal.
Add something like this before the FIND command. If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If And prefer xlFormulas to xlValues if you are looking for numbers or dates. Regards, Mishell "Neal Zimm" wrote in message ... Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
See comments below
Hi Joel, Thanks for the effort, I think. This will be harsh, but I'm writing this paragraph after completing the rest of what you're about to read. This is the first time I've received advice on this community that's just bad and wrong. Sorry, I think you took very little time and care with your response. It seems you do not know very much about optional passing of argument values and your VBA syntax looked sloppy. You do not have to respond to this posting, but I would be happy to discuss any of it if you think it's wrong. ------------------------------------------------------------------------------ 1) I know a lot about arguement passing. I'm not sure what VBA will do if you have an ariguenemt with no options like lookin instead of lookin:=xlvalues. You don't need the lookin as long as you have the correct number of commas. find is defined as expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) if you don't need the after you can skip by just having the commas expression.Find(What,, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) and the parameter don't have to be in the order listed as long as you include the type. for example set c = columns("C").find(what:="abc",lookin:=xlvalues,loo kat:=xlwhole) is the same as set c = columns("C").find(what:="abc",lookat:=xlwhole, lookin:=xlvalues) or set c = columns("C").find("abc",,xlvalues, xlwhole) You did this set c = columns("C").find("abc",,lookin, lookat) ------------------------------------------------------------------------------ I'm sorry I did not make it clearer in my posting what my real problem is. The proc DOES find strings very nicely. When vFind is loaded with a numeric value like 1300 it finds 1st occurence and dupes just fine. When vFind is loaded with a value like 1401.61, it's first cell is SOMETIMES NOT found and additional cells with this value are not found. I'm guessing it has something to do with the way the data is stored in the sheet, or the way I'm doing stuff like: vFind = Varname 'before the call. ---------------------------------------------------------------------------- 2) The worksheet truncates the stored number that gets displayed but real uses the full number in memory if you have 1401.61123456789 and you have a format that display 2 decimal digits you will see 1401.61 but the full number is still in memory and you must a find is looking at the entire number. ---------------------------------------------------------------------------- I DON'T think your changes have much to do with the above problem, AND, sorry to say, Many of them are Wrong or not needed. (I can't think of a way to "change" ---------------------------------------------------------------------------- I didn't see anything drastic with the code you except you had an unecessary IF statement. If Not Rng Is Nothing And Rng.Address < FirAdr Then I made other changes to the code to use methods that I was 100% sure would work that I used hundreds of times before. Since you were having problems I thought it was best to use know methods than to attempt to figure out if you non-standard code would work. I only saying you code is non-standard (not wrong) because it doesn't agree with the example that is given in the VBA help menu. I don't alway like the methods that are givin in the VBA help and use my own version when I think my code is more understandable. when I have problems with my code I usually go back to simple methods to debug the problem that I know will work. That all I was attempting in your case. ---------------------------------------------------------------------------- vFind inside the proc) . I have problems with some of your suggestions, but I'm still learning about ..find and .findnext, please comment on them so I can be sure. Thanks. "You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ. Proc argument: Optional bWhole As Boolean = True mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart Proc argument: Optional LookIn As Integer = xlValues Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values Call FindRngValues(.......,xlFormulas,,) I want the Sub to be able to find values OR formulas, (or constants in the cells) which are the numbers relating to my problem. -------------------------------------------------------------------- 3) Xlvalues can be one of three different options a) Comments (not constants) b) values c) formulas - no sure what they mean by this. I think it is looking for stating in the formula and not the value in the cell. the default is variant. I have no idea what variant has to do with comments and formulas. this really doesn't make sense. that is why I like to go back to code that I know works because the documentation often is confusing. Microsoft should hire better to to write the documentation. -------------------------------------------------------------------- "I modified the code the way microsoft would write the code." MSo provides examples, which I changed to meet MY functional needs, especially the ability to vary the meaning of the returned Found1Rng and DupeRng arguments. DupeRng either contains Found1Rng or not. See bOneRng argument. "I modified the code the way microsoft would write the code." My understanding of using named arguments is that you NEED them if you are leaving standard argument values out, or changing the left to right order of input. If AfterRng Is Nothing Then 'Why your change?? My ", ," leaves out the After argument, AND, you have ' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention, ' hence my use of the values in the LookIn and LookAt arguments for ' lookin values/formulas and lookat whole/part Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal Else 'Joel Set Rng = .Find(what:=vFind, after:=AfterRng, _ LookIn:=xlvalues,LookAt:=xlvalues) Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal 'Again, why your change ??? Since the first 4 values in .Find are what, after, 'lookin, lookat I saw no need for using named arguments, and you were 'careless here, you used xlValues for both LookIn: and LookAt: End If --------------------------------------------------------------------- 4) sorry for the typo ---------------------------------------------------------------------- "I removed an unecessay IF statement in the loop." I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am not making these changes. As I said in my problem statement, this proc is working just fine when vFind contains a string value. If Not Rng Is Nothing Then '1st find was successful Set Found1Rng = Rng 'capture 1st range FirAdr = Found1Rng.Address 'capture address of 1st found range Do 'try to find 2nd and more Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the ONLY argument 'Joel REMOVED line below ' If Not Rng Is Nothing And Rng.Address < FirAdr Then ' Well, you've blasted to smithereens a key part of my logic. ' 1. The code below will run whether or NOT .FindNext found something. ' I want the cell count and DupeRng ONLY for .FindNext that results in a ' find which typically is not the 1st find. ' 2. Further, suppose .FindNext works once, and then finds nothing more. ' Rng will be nothing and the DupeRng union statement will blow up as it ' requires not nothing arguments. lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If 'Your ERROR, you removed the If, but NOT the end if. Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from Until End if Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice of style as well as substance. I understand the change to While because you took out the If statement. BUT "While" with " Or Rng.Address = FirAdr" is DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps around and finds the 1st value again. This change will cause a NEVER ENDING loop when .findnext finds at least a second occurence of the vFind value. If you can get your code to run, try it out. ------------------------------------------------------------------------------ 5) the only time the last test below is needed is when the obj (RNG) is only one item (cell) Loop while not Rng Is Nothing Or Rng.Address < FirAdr 'changed from --------------------------------------------------------------------------------- "Neal Zimm" wrote: Hi Joel, Thanks for the effort, I think. This will be harsh, but I'm writing this paragraph after completing the rest of what you're about to read. This is the first time I've received advice on this community that's just bad and wrong. Sorry, I think you took very little time and care with your response. It seems you do not know very much about optional passing of argument values and your VBA syntax looked sloppy. You do not have to respond to this posting, but I would be happy to discuss any of it if you think it's wrong. I'm sorry I did not make it clearer in my posting what my real problem is. The proc DOES find strings very nicely. When vFind is loaded with a numeric value like 1300 it finds 1st occurence and dupes just fine. When vFind is loaded with a value like 1401.61, it's first cell is SOMETIMES NOT found and additional cells with this value are not found. I'm guessing it has something to do with the way the data is stored in the sheet, or the way I'm doing stuff like: vFind = Varname 'before the call. I DON'T think your changes have much to do with the above problem, AND, sorry to say, Many of them are Wrong or not needed. (I can't think of a way to "change" vFind inside the proc) . I have problems with some of your suggestions, but I'm still learning about .find and .findnext, please comment on them so I can be sure. Thanks. "You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ. Proc argument: Optional bWhole As Boolean = True mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart Proc argument: Optional LookIn As Integer = xlValues Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values Call FindRngValues(.......,xlFormulas,,) I want the Sub to be able to find values OR formulas, (or constants in the cells) which are the numbers relating to my problem. "I modified the code the way microsoft would write the code." MSo provides examples, which I changed to meet MY functional needs, especially the ability to vary the meaning of the returned Found1Rng and DupeRng arguments. DupeRng either contains Found1Rng or not. See bOneRng argument. "I modified the code the way microsoft would write the code." My understanding of using named arguments is that you NEED them if you are leaving standard argument values out, or changing the left to right order of input. If AfterRng Is Nothing Then 'Why your change?? My ", ," leaves out the After argument, AND, you have ' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention, ' hence my use of the values in the LookIn and LookAt arguments for ' lookin values/formulas and lookat whole/part Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal Else 'Joel Set Rng = .Find(what:=vFind, after:=AfterRng, _ LookIn:=xlvalues,LookAt:=xlvalues) Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal 'Again, why your change ??? Since the first 4 values in .Find are what, after, 'lookin, lookat I saw no need for using named arguments, and you were 'careless here, you used xlValues for both LookIn: and LookAt: End If "I removed an unecessay IF statement in the loop." I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am not making these changes. As I said in my problem statement, this proc is working just fine when vFind contains a string value. If Not Rng Is Nothing Then '1st find was successful Set Found1Rng = Rng 'capture 1st range FirAdr = Found1Rng.Address 'capture address of 1st found range Do 'try to find 2nd and more Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the ONLY argument 'Joel REMOVED line below ' If Not Rng Is Nothing And Rng.Address < FirAdr Then ' Well, you've blasted to smithereens a key part of my logic. ' 1. The code below will run whether or NOT .FindNext found something. ' I want the cell count and DupeRng ONLY for .FindNext that results in a ' find which typically is not the 1st find. ' 2. Further, suppose .FindNext works once, and then finds nothing more. ' Rng will be nothing and the DupeRng union statement will blow up as it ' requires not nothing arguments. lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If 'Your ERROR, you removed the If, but NOT the end if. Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from Until End if Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice of style as well as substance. I understand the change to While because you took out the If statement. BUT "While" with " Or Rng.Address = FirAdr" is DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps around and finds the 1st value again. This change will cause a NEVER ENDING loop when .findnext finds at least a second occurence of the vFind value. If you can get your code to run, try it out. -- Neal Z "Joel" wrote: I modified the code the way microsoft would write the code. You didn't specify lookin:=values and lookat:=xlwhole. I removed an unecessay IF statement in the loop and changed "Loop until" to "loop while", and added a NOT. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) Else Set Rng = .Find(what:=vFind, after:=AfterRng, LookIn:=xlvalues, LookAt:=xlvalues) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(after:=Rng) lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop while not Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub "Neal Zimm" wrote: Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Hi Mishell - Well, the saga continues.
I built the proc below to isolate .Find and .Findnext. I need to capture the first 'find' and to isolate any additional cells with the same value as 'Dupes'. Test cases #1 #2 #3 work swell without your "C" code. #4 and #4A also work without your code. #4B is where I hit the Jack Daniels, your code does not seem to matter. I'm using only 5 test data cells, values and formats are below. In case you'd like to run the proc. I am millimeters away from making a work around in the 'real' generalized sub as my permanent method. It's a plain old row loop using If Varname = cells(row,col).value Then .... and it works great. Go figure. I'd appreciate any further thoughts. You should be able to copy the sub below, key the data into a sheet, d1:d5, and run it. Just remember to select 1 cell in the test data range in the sheet for the AfterRng . Thanks. Neal Z. Sub A_Test_Find() Dim AfterRng As Range Dim DupeRng As Range Dim Found1Rng As Range Dim InRng As Range Dim Rng As Range Dim lCount As Long Dim LookAt As Integer Dim LookIn As Integer Dim sFirAdr As String Dim vFind As Variant ' #1 string testing, note this worked without your CStr ' Code worked as expected for string vFind's. ' h1 thru h5 = abc, [cleared], abd, abe, abe ' Set InRng = Range("h1:h5") ' vFind = "abe": LookIn = xlValues: LookAt = xlWhole ' vFind = "ab": LookIn = xlValues: LookAt = xlPart ' #2 date testing, note, this worked without your CDate ' Question: WHY does .Find NOT work with xlValues ? ' f1:f5 "mmm d", f1=Aug 10, f2:f4=Aug 28, f5=Aug 10 ' Set InRng = Range("f1:f5") ' vFind = DateValue("aug 28") ' LookIn = xlFormulas: LookAt = xlWhole ' #3 numeric testing, all values entered by hand, this worked without your CDbl ' Question: Same not work with xlValues ? ' For a hand entered number debug.print of a range(xx).value ' or range(xx).formula show the same number. ' It worked just fine with test data below. ' d1:d5 "number" 2 decimals, d1:d3 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = Range("d1").Value '1.56 12.34 range("D1").value ' LookIn = xlFormulas: LookAt = xlWhole ' #4 numeric testing, 4 hand entered #'s into cells, 1 formula cell. ' Numeric cells NOT found. ' It's 'seen' value in the cell is 12.34. ' "d2" was found using xlPart and xlFormulas in a diff test case. ' d1:d5 "number" 2 decimals, d3 formula is: = d2 ' d1:d2 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = 12.34 '1.56 12.34 range("D1").value "d2" ' LookAt = xlWhole ' LookIn = xlValues ' #4A This is screwy, using the value from the d3 cell with the formula, ' I was able to find d1 and d2 using xlFormulas, as expected. See #4B ' Your CDbl was not used. ' Set InRng = Range("d1:d5") ' vFind = Range("d3").Value ' LookAt = xlWhole ' LookIn = xlFormulas ' #4B The only change from 4A is xlValues for LookIn. ' With and without CDbl, no cell was found. Oh well. ' Same cell contents as #4. Set InRng = Range("d1:d5") vFind = Range("d3").Value 'vFind = 12.34 'still no go with this line LookAt = xlWhole LookIn = xlValues Cells.Interior.ColorIndex = xlNone 'to test Set AfterRng = Selection 'to test varying the after cell With InRng 'AfterRng, If optional input Arg is nothing, this proc values 'AfterRng as rightmost bottom cell so find starts AT top left cell, 'NOT MSo default of next after top left. If AfterRng Is Nothing Then _ Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _ (.Column + .Columns.Count - 1)) If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) If Not Rng Is Nothing Then Set Found1Rng = Rng sFirAdr = Found1Rng.Address Found1Rng.Interior.ColorIndex = 35 'testing only Do Set Rng = .FindNext(Rng) If Rng Is Nothing Or Rng.Address = sFirAdr Then Exit Do Else lCount = lCount + 1 If lCount = 1 Then Set DupeRng = Rng _ Else Set DupeRng = Union(DupeRng, Rng) End If Loop While Not Rng Is Nothing And Rng.Address < sFirAdr End If End With If Not DupeRng Is Nothing Then DupeRng.Interior.ColorIndex = 6 'testing only End Sub -- Neal Z "Mishell" wrote: Hi Neal. Add something like this before the FIND command. If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If And prefer xlFormulas to xlValues if you are looking for numbers or dates. Regards, Mishell "Neal Zimm" wrote in message ... Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Each cell in a worksheet has a Formula property, a Text property and a Value
Property. The Value property is ignored by the FIND command. The search of the FIND command is made only in the Formula Property (LookIn:=xlFormulas) or in the Text Property (LookIn:=xlValues). The Text property is dependant of the View Format chosen for the cell. It does not necessarily contain the real value of a number or of a date. If the cell does not have a formula, then the Formula property contains the real value of the cell and this value can then be found by the FIND command using LookIn:=xlFormulas. If you are looking for a defined format of a value, use LookIn:=xlValues to look in the Text property of the cells. If you are looking for a value whatever the cell format is, use LookIn:=xlFormulas to look in the Formula property of the cells. The VarType of the searched item is also very important. The value of the date Jan-04-2006 is 38721. To find the number 38721, give the searched item the Double Type (CDbl(searchedItem)) or the String Type (CStr(searchedItem)). To find the date corresponding to the value 38721, give the searched item the Date Type (CDate(searchedItem)). The result will include all the corresponding cells with any Date format, plus the corresponding cells with a Text format but containing a string with the "Short Date" format of the searched date. Regards, Mishell "Neal Zimm" wrote in message ... Hi Mishell - Well, the saga continues. I built the proc below to isolate .Find and .Findnext. I need to capture the first 'find' and to isolate any additional cells with the same value as 'Dupes'. Test cases #1 #2 #3 work swell without your "C" code. #4 and #4A also work without your code. #4B is where I hit the Jack Daniels, your code does not seem to matter. I'm using only 5 test data cells, values and formats are below. In case you'd like to run the proc. I am millimeters away from making a work around in the 'real' generalized sub as my permanent method. It's a plain old row loop using If Varname = cells(row,col).value Then .... and it works great. Go figure. I'd appreciate any further thoughts. You should be able to copy the sub below, key the data into a sheet, d1:d5, and run it. Just remember to select 1 cell in the test data range in the sheet for the AfterRng . Thanks. Neal Z. Sub A_Test_Find() Dim AfterRng As Range Dim DupeRng As Range Dim Found1Rng As Range Dim InRng As Range Dim Rng As Range Dim lCount As Long Dim LookAt As Integer Dim LookIn As Integer Dim sFirAdr As String Dim vFind As Variant ' #1 string testing, note this worked without your CStr ' Code worked as expected for string vFind's. ' h1 thru h5 = abc, [cleared], abd, abe, abe ' Set InRng = Range("h1:h5") ' vFind = "abe": LookIn = xlValues: LookAt = xlWhole ' vFind = "ab": LookIn = xlValues: LookAt = xlPart ' #2 date testing, note, this worked without your CDate ' Question: WHY does .Find NOT work with xlValues ? ' f1:f5 "mmm d", f1=Aug 10, f2:f4=Aug 28, f5=Aug 10 ' Set InRng = Range("f1:f5") ' vFind = DateValue("aug 28") ' LookIn = xlFormulas: LookAt = xlWhole ' #3 numeric testing, all values entered by hand, this worked without your CDbl ' Question: Same not work with xlValues ? ' For a hand entered number debug.print of a range(xx).value ' or range(xx).formula show the same number. ' It worked just fine with test data below. ' d1:d5 "number" 2 decimals, d1:d3 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = Range("d1").Value '1.56 12.34 range("D1").value ' LookIn = xlFormulas: LookAt = xlWhole ' #4 numeric testing, 4 hand entered #'s into cells, 1 formula cell. ' Numeric cells NOT found. ' It's 'seen' value in the cell is 12.34. ' "d2" was found using xlPart and xlFormulas in a diff test case. ' d1:d5 "number" 2 decimals, d3 formula is: = d2 ' d1:d2 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = 12.34 '1.56 12.34 range("D1").value "d2" ' LookAt = xlWhole ' LookIn = xlValues ' #4A This is screwy, using the value from the d3 cell with the formula, ' I was able to find d1 and d2 using xlFormulas, as expected. See #4B ' Your CDbl was not used. ' Set InRng = Range("d1:d5") ' vFind = Range("d3").Value ' LookAt = xlWhole ' LookIn = xlFormulas ' #4B The only change from 4A is xlValues for LookIn. ' With and without CDbl, no cell was found. Oh well. ' Same cell contents as #4. Set InRng = Range("d1:d5") vFind = Range("d3").Value 'vFind = 12.34 'still no go with this line LookAt = xlWhole LookIn = xlValues Cells.Interior.ColorIndex = xlNone 'to test Set AfterRng = Selection 'to test varying the after cell With InRng 'AfterRng, If optional input Arg is nothing, this proc values 'AfterRng as rightmost bottom cell so find starts AT top left cell, 'NOT MSo default of next after top left. If AfterRng Is Nothing Then _ Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _ (.Column + .Columns.Count - 1)) If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) If Not Rng Is Nothing Then Set Found1Rng = Rng sFirAdr = Found1Rng.Address Found1Rng.Interior.ColorIndex = 35 'testing only Do Set Rng = .FindNext(Rng) If Rng Is Nothing Or Rng.Address = sFirAdr Then Exit Do Else lCount = lCount + 1 If lCount = 1 Then Set DupeRng = Rng _ Else Set DupeRng = Union(DupeRng, Rng) End If Loop While Not Rng Is Nothing And Rng.Address < sFirAdr End If End With If Not DupeRng Is Nothing Then DupeRng.Interior.ColorIndex = 6 'testing only End Sub -- Neal Z "Mishell" wrote: Hi Neal. Add something like this before the FIND command. If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If And prefer xlFormulas to xlValues if you are looking for numbers or dates. Regards, Mishell "Neal Zimm" wrote in message ... Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If Loop Until Rng Is Nothing Or Rng.Address = FirAdr End If End With If Not Found1Rng Is Nothing And bOneRng Then If DupeRng Is Nothing Then Set DupeRng = Found1Rng lCellCount = 1 Else Set DupeRng = Union(Found1Rng, DupeRng) lCellCount = lCellCount + 1 End If End If If Not DupeRng Is Nothing Then iAreas = DupeRng.Areas.Count 'mainline end End Sub -- Neal Z |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Mishell -
Thanks. Your explanation of the text property equating to xlValues clears the fog. I had thought xlValues kinda equated to range(xxx).value more than was true. I've adjusted the default optional values in the proc statement below for the tool that I've built. The boolean Whole and Formulas vars value look in and look at variables. It's working pretty well now. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional bFormulas As Boolean = True, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0, _ Optional bDebugPrt As Boolean = False) ' Return data ranges containing vFind. ' Outputs: See also bOneRng input. ' Found1Rng, not nothing holds 1st find. DupeRng not nothing holds ADDITIONAL vFind's. ' lCount = count of cells in DupeRng. iAreas = count of areas in DupeRng. ' Inputs: InRng = search range, vFind = What to look for. ' bWhole, look in xlWhole, F = look in xlPart ' AfterRng, Nothing= proc values AfterRng as rightmost bottom cell so find starts AT top left cell, NOT MSo default of next after top left. ' bFormulas, look in xlFormulas, F = xlValues ' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted. ' bDebugPrt, Debug.Print Sub's results -- Neal Z "Mishell" wrote: Each cell in a worksheet has a Formula property, a Text property and a Value Property. The Value property is ignored by the FIND command. The search of the FIND command is made only in the Formula Property (LookIn:=xlFormulas) or in the Text Property (LookIn:=xlValues). The Text property is dependant of the View Format chosen for the cell. It does not necessarily contain the real value of a number or of a date. If the cell does not have a formula, then the Formula property contains the real value of the cell and this value can then be found by the FIND command using LookIn:=xlFormulas. If you are looking for a defined format of a value, use LookIn:=xlValues to look in the Text property of the cells. If you are looking for a value whatever the cell format is, use LookIn:=xlFormulas to look in the Formula property of the cells. The VarType of the searched item is also very important. The value of the date Jan-04-2006 is 38721. To find the number 38721, give the searched item the Double Type (CDbl(searchedItem)) or the String Type (CStr(searchedItem)). To find the date corresponding to the value 38721, give the searched item the Date Type (CDate(searchedItem)). The result will include all the corresponding cells with any Date format, plus the corresponding cells with a Text format but containing a string with the "Short Date" format of the searched date. Regards, Mishell "Neal Zimm" wrote in message ... Hi Mishell - Well, the saga continues. I built the proc below to isolate .Find and .Findnext. I need to capture the first 'find' and to isolate any additional cells with the same value as 'Dupes'. Test cases #1 #2 #3 work swell without your "C" code. #4 and #4A also work without your code. #4B is where I hit the Jack Daniels, your code does not seem to matter. I'm using only 5 test data cells, values and formats are below. In case you'd like to run the proc. I am millimeters away from making a work around in the 'real' generalized sub as my permanent method. It's a plain old row loop using If Varname = cells(row,col).value Then .... and it works great. Go figure. I'd appreciate any further thoughts. You should be able to copy the sub below, key the data into a sheet, d1:d5, and run it. Just remember to select 1 cell in the test data range in the sheet for the AfterRng . Thanks. Neal Z. Sub A_Test_Find() Dim AfterRng As Range Dim DupeRng As Range Dim Found1Rng As Range Dim InRng As Range Dim Rng As Range Dim lCount As Long Dim LookAt As Integer Dim LookIn As Integer Dim sFirAdr As String Dim vFind As Variant ' #1 string testing, note this worked without your CStr ' Code worked as expected for string vFind's. ' h1 thru h5 = abc, [cleared], abd, abe, abe ' Set InRng = Range("h1:h5") ' vFind = "abe": LookIn = xlValues: LookAt = xlWhole ' vFind = "ab": LookIn = xlValues: LookAt = xlPart ' #2 date testing, note, this worked without your CDate ' Question: WHY does .Find NOT work with xlValues ? ' f1:f5 "mmm d", f1=Aug 10, f2:f4=Aug 28, f5=Aug 10 ' Set InRng = Range("f1:f5") ' vFind = DateValue("aug 28") ' LookIn = xlFormulas: LookAt = xlWhole ' #3 numeric testing, all values entered by hand, this worked without your CDbl ' Question: Same not work with xlValues ? ' For a hand entered number debug.print of a range(xx).value ' or range(xx).formula show the same number. ' It worked just fine with test data below. ' d1:d5 "number" 2 decimals, d1:d3 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = Range("d1").Value '1.56 12.34 range("D1").value ' LookIn = xlFormulas: LookAt = xlWhole ' #4 numeric testing, 4 hand entered #'s into cells, 1 formula cell. ' Numeric cells NOT found. ' It's 'seen' value in the cell is 12.34. ' "d2" was found using xlPart and xlFormulas in a diff test case. ' d1:d5 "number" 2 decimals, d3 formula is: = d2 ' d1:d2 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = 12.34 '1.56 12.34 range("D1").value "d2" ' LookAt = xlWhole ' LookIn = xlValues ' #4A This is screwy, using the value from the d3 cell with the formula, ' I was able to find d1 and d2 using xlFormulas, as expected. See #4B ' Your CDbl was not used. ' Set InRng = Range("d1:d5") ' vFind = Range("d3").Value ' LookAt = xlWhole ' LookIn = xlFormulas ' #4B The only change from 4A is xlValues for LookIn. ' With and without CDbl, no cell was found. Oh well. ' Same cell contents as #4. Set InRng = Range("d1:d5") vFind = Range("d3").Value 'vFind = 12.34 'still no go with this line LookAt = xlWhole LookIn = xlValues Cells.Interior.ColorIndex = xlNone 'to test Set AfterRng = Selection 'to test varying the after cell With InRng 'AfterRng, If optional input Arg is nothing, this proc values 'AfterRng as rightmost bottom cell so find starts AT top left cell, 'NOT MSo default of next after top left. If AfterRng Is Nothing Then _ Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _ (.Column + .Columns.Count - 1)) If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) If Not Rng Is Nothing Then Set Found1Rng = Rng sFirAdr = Found1Rng.Address Found1Rng.Interior.ColorIndex = 35 'testing only Do Set Rng = .FindNext(Rng) If Rng Is Nothing Or Rng.Address = sFirAdr Then Exit Do Else lCount = lCount + 1 If lCount = 1 Then Set DupeRng = Rng _ Else Set DupeRng = Union(DupeRng, Rng) End If Loop While Not Rng Is Nothing And Rng.Address < sFirAdr End If End With If Not DupeRng Is Nothing Then DupeRng.Interior.ColorIndex = 6 'testing only End Sub -- Neal Z "Mishell" wrote: Hi Neal. Add something like this before the FIND command. If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If And prefer xlFormulas to xlValues if you are looking for numbers or dates. Regards, Mishell "Neal Zimm" wrote in message ... Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Thanks to you Neal. Glad if I could help you with the FIND issue.
Regards, Mishell "Neal Zimm" wrote in message ... Mishell - Thanks. Your explanation of the text property equating to xlValues clears the fog. I had thought xlValues kinda equated to range(xxx).value more than was true. I've adjusted the default optional values in the proc statement below for the tool that I've built. The boolean Whole and Formulas vars value look in and look at variables. It's working pretty well now. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional bFormulas As Boolean = True, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0, _ Optional bDebugPrt As Boolean = False) ' Return data ranges containing vFind. ' Outputs: See also bOneRng input. ' Found1Rng, not nothing holds 1st find. DupeRng not nothing holds ADDITIONAL vFind's. ' lCount = count of cells in DupeRng. iAreas = count of areas in DupeRng. ' Inputs: InRng = search range, vFind = What to look for. ' bWhole, look in xlWhole, F = look in xlPart ' AfterRng, Nothing= proc values AfterRng as rightmost bottom cell so find starts AT top left cell, NOT MSo default of next after top left. ' bFormulas, look in xlFormulas, F = xlValues ' bOneRng, DupeRng includes Found1Rng. lCount, iAreas are adjusted. ' bDebugPrt, Debug.Print Sub's results -- Neal Z "Mishell" wrote: Each cell in a worksheet has a Formula property, a Text property and a Value Property. The Value property is ignored by the FIND command. The search of the FIND command is made only in the Formula Property (LookIn:=xlFormulas) or in the Text Property (LookIn:=xlValues). The Text property is dependant of the View Format chosen for the cell. It does not necessarily contain the real value of a number or of a date. If the cell does not have a formula, then the Formula property contains the real value of the cell and this value can then be found by the FIND command using LookIn:=xlFormulas. If you are looking for a defined format of a value, use LookIn:=xlValues to look in the Text property of the cells. If you are looking for a value whatever the cell format is, use LookIn:=xlFormulas to look in the Formula property of the cells. The VarType of the searched item is also very important. The value of the date Jan-04-2006 is 38721. To find the number 38721, give the searched item the Double Type (CDbl(searchedItem)) or the String Type (CStr(searchedItem)). To find the date corresponding to the value 38721, give the searched item the Date Type (CDate(searchedItem)). The result will include all the corresponding cells with any Date format, plus the corresponding cells with a Text format but containing a string with the "Short Date" format of the searched date. Regards, Mishell "Neal Zimm" wrote in message ... Hi Mishell - Well, the saga continues. I built the proc below to isolate .Find and .Findnext. I need to capture the first 'find' and to isolate any additional cells with the same value as 'Dupes'. Test cases #1 #2 #3 work swell without your "C" code. #4 and #4A also work without your code. #4B is where I hit the Jack Daniels, your code does not seem to matter. I'm using only 5 test data cells, values and formats are below. In case you'd like to run the proc. I am millimeters away from making a work around in the 'real' generalized sub as my permanent method. It's a plain old row loop using If Varname = cells(row,col).value Then .... and it works great. Go figure. I'd appreciate any further thoughts. You should be able to copy the sub below, key the data into a sheet, d1:d5, and run it. Just remember to select 1 cell in the test data range in the sheet for the AfterRng . Thanks. Neal Z. Sub A_Test_Find() Dim AfterRng As Range Dim DupeRng As Range Dim Found1Rng As Range Dim InRng As Range Dim Rng As Range Dim lCount As Long Dim LookAt As Integer Dim LookIn As Integer Dim sFirAdr As String Dim vFind As Variant ' #1 string testing, note this worked without your CStr ' Code worked as expected for string vFind's. ' h1 thru h5 = abc, [cleared], abd, abe, abe ' Set InRng = Range("h1:h5") ' vFind = "abe": LookIn = xlValues: LookAt = xlWhole ' vFind = "ab": LookIn = xlValues: LookAt = xlPart ' #2 date testing, note, this worked without your CDate ' Question: WHY does .Find NOT work with xlValues ? ' f1:f5 "mmm d", f1=Aug 10, f2:f4=Aug 28, f5=Aug 10 ' Set InRng = Range("f1:f5") ' vFind = DateValue("aug 28") ' LookIn = xlFormulas: LookAt = xlWhole ' #3 numeric testing, all values entered by hand, this worked without your CDbl ' Question: Same not work with xlValues ? ' For a hand entered number debug.print of a range(xx).value ' or range(xx).formula show the same number. ' It worked just fine with test data below. ' d1:d5 "number" 2 decimals, d1:d3 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = Range("d1").Value '1.56 12.34 range("D1").value ' LookIn = xlFormulas: LookAt = xlWhole ' #4 numeric testing, 4 hand entered #'s into cells, 1 formula cell. ' Numeric cells NOT found. ' It's 'seen' value in the cell is 12.34. ' "d2" was found using xlPart and xlFormulas in a diff test case. ' d1:d5 "number" 2 decimals, d3 formula is: = d2 ' d1:d2 = 12.34, d4:d5 = 1.56 ' Set InRng = Range("d1:d5") ' vFind = 12.34 '1.56 12.34 range("D1").value "d2" ' LookAt = xlWhole ' LookIn = xlValues ' #4A This is screwy, using the value from the d3 cell with the formula, ' I was able to find d1 and d2 using xlFormulas, as expected. See #4B ' Your CDbl was not used. ' Set InRng = Range("d1:d5") ' vFind = Range("d3").Value ' LookAt = xlWhole ' LookIn = xlFormulas ' #4B The only change from 4A is xlValues for LookIn. ' With and without CDbl, no cell was found. Oh well. ' Same cell contents as #4. Set InRng = Range("d1:d5") vFind = Range("d3").Value 'vFind = 12.34 'still no go with this line LookAt = xlWhole LookIn = xlValues Cells.Interior.ColorIndex = xlNone 'to test Set AfterRng = Selection 'to test varying the after cell With InRng 'AfterRng, If optional input Arg is nothing, this proc values 'AfterRng as rightmost bottom cell so find starts AT top left cell, 'NOT MSo default of next after top left. If AfterRng Is Nothing Then _ Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _ (.Column + .Columns.Count - 1)) If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) If Not Rng Is Nothing Then Set Found1Rng = Rng sFirAdr = Found1Rng.Address Found1Rng.Interior.ColorIndex = 35 'testing only Do Set Rng = .FindNext(Rng) If Rng Is Nothing Or Rng.Address = sFirAdr Then Exit Do Else lCount = lCount + 1 If lCount = 1 Then Set DupeRng = Rng _ Else Set DupeRng = Union(DupeRng, Rng) End If Loop While Not Rng Is Nothing And Rng.Address < sFirAdr End If End With If Not DupeRng Is Nothing Then DupeRng.Interior.ColorIndex = 6 'testing only End Sub -- Neal Z "Mishell" wrote: Hi Neal. Add something like this before the FIND command. If IsNumeric(vFind) Then vFind = CDbl(vFind) ElseIf IsDate(vFind) Then vFind = CDate(vFind) Else vFind = CStr(vFind) End If And prefer xlFormulas to xlValues if you are looking for numbers or dates. Regards, Mishell "Neal Zimm" wrote in message ... Hi - I built the proc below as a tool using the .Find method. I'm testing it. It works OK when vFind is loaded with a string or an integer number. It does NOT find numeric values such as 1401.61 or any date values. The Mso help on .Find says "any data type" for the variant find argument. Examples prior to calling FindRngValues Dim DtTest as date Dim nValue as single Dim vFind as variant nvalue = range(whatever).value '1300.00 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were found. nvalue = range(whatever).value '1401.61 (cell format number, 2 decimals) vFind = nvalue Call .... ' values were NOT found, but I see them in the worksheet cells. Same for DtTest values where cell formatted as date, "m/d/yyyy" Cell values were not found. What can I do to find these types of data ? (I have row loops that work, but I like the flexibility of proc below) Thanks, Neal Z. Sub FindRngValues(InRng As Range, vFind, DupeRng As Range, lCellCount As Long, _ Optional Found1Rng As Range = Nothing, Optional bWhole As Boolean = True, _ Optional AfterRng As Range = Nothing, Optional LookIn As Integer = xlValues, _ Optional bOneRng As Boolean = False, Optional iAreas As Integer = 0) 'Return data ranges containing vFind. ' DupeRng is nothing on not found or no dupes. ' lCellCount = count of cells in DupeRng. ' iAreas = count of areas in DupeRng. ' Found1Rng, not nothing has 1st find. DupeRng contains the ADDITIONAL duplicate values. ' bWhole T= xlWhole F= xlPart, ' To NOT use top left of InRng as After parm, value input AfterRng arg with 1 cell. ' LookIn xlValues or xlFormulas ' bOneRng, F=Dupe and Found1 ranges, iAreas as defined above. T= DupeRng contains Found1Rng, ' iAreas and lCellCount are for the union'd DupeRng. Dim Rng As Range Dim FirAdr As String Dim LookAt As Integer 'xlwhole or xlpart 'mainline start Set DupeRng = Nothing iAreas = 0 lCellCount = 0 Set Found1Rng = Nothing If InRng Is Nothing Then Exit Sub If VarType(vFind) = vbString Then If vFind = "" Then Exit Sub If bWhole Then LookAt = xlWhole Else LookAt = xlPart With InRng If AfterRng Is Nothing Then Set Rng = .Find(vFind, , LookIn, LookAt) Else Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) End If If Not Rng Is Nothing Then Set Found1Rng = Rng FirAdr = Found1Rng.Address Do Set Rng = .FindNext(Rng) If Not Rng Is Nothing And Rng.Address < FirAdr Then lCellCount = lCellCount + 1 If lCellCount = 1 Then |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
.Find method with numeric and date values, Aug2009
Joel -
I apologize for getting so hot before. It stemmed from the fact that the MSo help example for .Find and .Findnext contains an error when I finally ran it. I didn't find that out until after I responded to your post. Turns out the real cause is the value I put into the vFind variable, and as it turns out, valuing the LookIn argument to xlValues, when for most numeric data, it's far better to LookIn xlFormulas. (At least if the value your search for is not the result of a 'real' formula in the cell. That's the situation in my App. Thanks again for the considerable time you spent on this. The working version of the find and next loop is below. Neal Z With InRng If AfterRng Is Nothing Then 'default to last cell in range to search the 1st. Set AfterRng = .Worksheet.Cells((.Row + .Rows.Count - 1), _ (.Column + .Columns.Count - 1)) End If Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) If Not Rng Is Nothing Then Set Found1Rng = Rng sFirAdr = Found1Rng.Address Do While Not Rng is Nothing Set Rng = .FindNext(Rng) If Rng Is Nothing Or Rng.Address = sFirAdr Then Exit Do Else lCount = lCount + 1 If lCount = 1 Then Set DupeRng = Rng Else Set DupeRng = _ Union(DupeRng, Rng) End If Loop End If End With -- Neal Z "Joel" wrote: See comments below Hi Joel, Thanks for the effort, I think. This will be harsh, but I'm writing this paragraph after completing the rest of what you're about to read. This is the first time I've received advice on this community that's just bad and wrong. Sorry, I think you took very little time and care with your response. It seems you do not know very much about optional passing of argument values and your VBA syntax looked sloppy. You do not have to respond to this posting, but I would be happy to discuss any of it if you think it's wrong. ------------------------------------------------------------------------------ 1) I know a lot about arguement passing. I'm not sure what VBA will do if you have an ariguenemt with no options like lookin instead of lookin:=xlvalues. You don't need the lookin as long as you have the correct number of commas. find is defined as expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) if you don't need the after you can skip by just having the commas expression.Find(What,, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat) and the parameter don't have to be in the order listed as long as you include the type. for example set c = columns("C").find(what:="abc",lookin:=xlvalues,loo kat:=xlwhole) is the same as set c = columns("C").find(what:="abc",lookat:=xlwhole, lookin:=xlvalues) or set c = columns("C").find("abc",,xlvalues, xlwhole) You did this set c = columns("C").find("abc",,lookin, lookat) ------------------------------------------------------------------------------ I'm sorry I did not make it clearer in my posting what my real problem is. The proc DOES find strings very nicely. When vFind is loaded with a numeric value like 1300 it finds 1st occurence and dupes just fine. When vFind is loaded with a value like 1401.61, it's first cell is SOMETIMES NOT found and additional cells with this value are not found. I'm guessing it has something to do with the way the data is stored in the sheet, or the way I'm doing stuff like: vFind = Varname 'before the call. ---------------------------------------------------------------------------- 2) The worksheet truncates the stored number that gets displayed but real uses the full number in memory if you have 1401.61123456789 and you have a format that display 2 decimal digits you will see 1401.61 but the full number is still in memory and you must a find is looking at the entire number. ---------------------------------------------------------------------------- I DON'T think your changes have much to do with the above problem, AND, sorry to say, Many of them are Wrong or not needed. (I can't think of a way to "change" ---------------------------------------------------------------------------- I didn't see anything drastic with the code you except you had an unecessary IF statement. If Not Rng Is Nothing And Rng.Address < FirAdr Then I made other changes to the code to use methods that I was 100% sure would work that I used hundreds of times before. Since you were having problems I thought it was best to use know methods than to attempt to figure out if you non-standard code would work. I only saying you code is non-standard (not wrong) because it doesn't agree with the example that is given in the VBA help menu. I don't alway like the methods that are givin in the VBA help and use my own version when I think my code is more understandable. when I have problems with my code I usually go back to simple methods to debug the problem that I know will work. That all I was attempting in your case. ---------------------------------------------------------------------------- vFind inside the proc) . I have problems with some of your suggestions, but I'm still learning about .find and .findnext, please comment on them so I can be sure. Thanks. "You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ. Proc argument: Optional bWhole As Boolean = True mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart Proc argument: Optional LookIn As Integer = xlValues Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values Call FindRngValues(.......,xlFormulas,,) I want the Sub to be able to find values OR formulas, (or constants in the cells) which are the numbers relating to my problem. -------------------------------------------------------------------- 3) Xlvalues can be one of three different options a) Comments (not constants) b) values c) formulas - no sure what they mean by this. I think it is looking for stating in the formula and not the value in the cell. the default is variant. I have no idea what variant has to do with comments and formulas. this really doesn't make sense. that is why I like to go back to code that I know works because the documentation often is confusing. Microsoft should hire better to to write the documentation. -------------------------------------------------------------------- "I modified the code the way microsoft would write the code." MSo provides examples, which I changed to meet MY functional needs, especially the ability to vary the meaning of the returned Found1Rng and DupeRng arguments. DupeRng either contains Found1Rng or not. See bOneRng argument. "I modified the code the way microsoft would write the code." My understanding of using named arguments is that you NEED them if you are leaving standard argument values out, or changing the left to right order of input. If AfterRng Is Nothing Then 'Why your change?? My ", ," leaves out the After argument, AND, you have ' FORCED this proc to ALWAYS look for WHOLE cell VALUES; NOT my intention, ' hence my use of the values in the LookIn and LookAt arguments for ' lookin values/formulas and lookat whole/part Set Rng = .Find(what:=vFind, LookIn:=xlvalues, LookAt:=xlwhole) 'Joel Set Rng = .Find(vFind, , LookIn, LookAt) 'Neal Else 'Joel Set Rng = .Find(what:=vFind, after:=AfterRng, _ LookIn:=xlvalues,LookAt:=xlvalues) Set Rng = .Find(vFind, AfterRng, LookIn, LookAt) 'Neal 'Again, why your change ??? Since the first 4 values in .Find are what, after, 'lookin, lookat I saw no need for using named arguments, and you were 'careless here, you used xlValues for both LookIn: and LookAt: End If --------------------------------------------------------------------- 4) sorry for the typo ---------------------------------------------------------------------- "I removed an unecessay IF statement in the loop." I'm pretty damn sure you are wrong, Joel, see my comments in the code. I am not making these changes. As I said in my problem statement, this proc is working just fine when vFind contains a string value. If Not Rng Is Nothing Then '1st find was successful Set Found1Rng = Rng 'capture 1st range FirAdr = Found1Rng.Address 'capture address of 1st found range Do 'try to find 2nd and more Set Rng = .FindNext(after:=Rng) 'after:= is redundant, it's the ONLY argument 'Joel REMOVED line below ' If Not Rng Is Nothing And Rng.Address < FirAdr Then ' Well, you've blasted to smithereens a key part of my logic. ' 1. The code below will run whether or NOT .FindNext found something. ' I want the cell count and DupeRng ONLY for .FindNext that results in a ' find which typically is not the 1st find. ' 2. Further, suppose .FindNext works once, and then finds nothing more. ' Rng will be nothing and the DupeRng union statement will blow up as it ' requires not nothing arguments. lCellCount = lCellCount + 1 If lCellCount = 1 Then Set DupeRng = Rng Else Set DupeRng = Union(DupeRng, Rng) End If End If 'Your ERROR, you removed the If, but NOT the end if. Loop while not Rng Is Nothing Or Rng.Address = FirAdr 'changed from Until End if Last BUT not least, Do, Loop Until versus Do While, Loop can be a choice of style as well as substance. I understand the change to While because you took out the If statement. BUT "While" with " Or Rng.Address = FirAdr" is DREADFULLY WRONG. The loop is supposed to QUIT when .findnext wraps around and finds the 1st value again. This change will cause a NEVER ENDING loop when .findnext finds at least a second occurence of the vFind value. If you can get your code to run, try it out. ------------------------------------------------------------------------------ 5) the only time the last test below is needed is when the obj (RNG) is only one item (cell) Loop while not Rng Is Nothing Or Rng.Address < FirAdr 'changed from --------------------------------------------------------------------------------- "Neal Zimm" wrote: Hi Joel, Thanks for the effort, I think. This will be harsh, but I'm writing this paragraph after completing the rest of what you're about to read. This is the first time I've received advice on this community that's just bad and wrong. Sorry, I think you took very little time and care with your response. It seems you do not know very much about optional passing of argument values and your VBA syntax looked sloppy. You do not have to respond to this posting, but I would be happy to discuss any of it if you think it's wrong. I'm sorry I did not make it clearer in my posting what my real problem is. The proc DOES find strings very nicely. When vFind is loaded with a numeric value like 1300 it finds 1st occurence and dupes just fine. When vFind is loaded with a value like 1401.61, it's first cell is SOMETIMES NOT found and additional cells with this value are not found. I'm guessing it has something to do with the way the data is stored in the sheet, or the way I'm doing stuff like: vFind = Varname 'before the call. I DON'T think your changes have much to do with the above problem, AND, sorry to say, Many of them are Wrong or not needed. (I can't think of a way to "change" vFind inside the proc) . I have problems with some of your suggestions, but I'm still learning about .find and .findnext, please comment on them so I can be sure. Thanks. "You didn't specify lookin:=values and lookat:=xlwhole." I beg to differ. Proc argument: Optional bWhole As Boolean = True mainline code: If bWhole Then LookAt = xlWhole Else LookAt = xlPart Proc argument: Optional LookIn As Integer = xlValues Call examples: Call FindRngValues(.......,xlValues,,) 'to look for values Call FindRngValues(.......,xlFormulas,,) I want the Sub to be able to find values OR formulas, (or constants in the cells) which are the numbers relating to my problem. "I modified the code the way microsoft would write the code." MSo provides examples, which I changed to meet MY functional needs, especially the ability to vary the meaning of the returned Found1Rng and DupeRng arguments. DupeRng either contains Found1Rng or not. See bOneRng argument. "I modified the code the way microsoft would write the code." My understanding of using named arguments is that you NEED them if you are |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Method Used within Values in an Array of Ranges | Excel Programming | |||
xls vba find method to find row that contains the current date | Excel Programming | |||
Find method - finding multiple values | Excel Programming | |||
Using variables to make a date and using find method to find that. | Excel Programming | |||
VLOOKUP cannot find numeric values | Excel Programming |