Hi all
Im having a little trouble with the Lookin and Lookat parameters in the Find function, as I want to get the xlValues/xlFormulas and xlWhole/xlPart options from a cell value, like below:
SearchVariants.png
Im pretty sure the problem is due to the variable types, but not sure how to fix it.
So this simple macro works:
But this does not:Sub Simple() Dim Found As Range Set Found = ActiveSheet.Range("A1:A5").Find("Test", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Debug.Print Found.Address End Sub
I can work around it, by adding this If else section, where I convert for example "xlValues" to xlValues, but how can I avoid that?:Sub NotWorking() Dim Found As Range Dim MatchCaseMode As Boolean Dim LookInMode As Variant Dim LookAtMode As Variant MatchCaseMode = ActiveSheet.Range("D2").Value LookInMode = ActiveSheet.Range("D3").Value LookAtMode = ActiveSheet.Range("D4").Value Set Found = ActiveSheet.Range("A1:A5").Find("Test", LookIn:=LookInMode, LookAt:=LookAtMode, MatchCase:=MatchCaseMode) Debug.Print Found.Address End Sub
Best regardsSub Working() Dim Found As Range Dim MatchCaseMode As Boolean Dim LookInMode As Variant Dim LookAtMode As Variant MatchCaseMode = ActiveSheet.Range("D2").Value LookInMode = ActiveSheet.Range("D3").Value LookAtMode = ActiveSheet.Range("D4").Value If LookInMode = "xlValues" Then LookInMode = xlValues ElseIf LookInMode = "xlFormulas" Then LookInMode = xlFormulas End If If LookAtMode = "xlWhole" Then LookAtMode = xlWhole ElseIf LookAtMode = "xlPart" Then LookAtMode = xlPart End If Set Found = ActiveSheet.Range("A1:A5").Find("Test", LookIn:=LookInMode, LookAt:=LookAtMode, MatchCase:=MatchCaseMode) Debug.Print Found.Address End Sub
Imbizile
Bookmarks