View Full Version : Find Function and Merged Cells
EirikDaude
11-30-2013, 05:27 PM
I'm having a bit of trouble with this code snippet:
Option Explicit
Function findInSheet(worksheetWithData As Worksheet) As Range
Dim rangeWithData As Range
With worksheetWithData
' Set what area we can find values in
Set rangeWithData = .Cells.Find("Test", LookIn:=xlValues, MatchCase:=False)
Set rangeWithData = Range(rangeWithData, Cells(Rows.CountLarge, ColLett(rangeWithData.Column)).End(xlUp))
End With
End Function
What happens is that .Find() can't locate the value (or at least whatever it finds can't be set to a range :P), and I get an error in the next line because the range isn't set yet. The string is in cell A1 of the worksheet, I assume that the reason the function can't find it is that cell A1 and A2 are merged together.
Any suggestions on how I can solve this problem without unmerging the cells?
-edit- Added sample workbook: 10898
EirikDaude
12-03-2013, 10:50 PM
Hmm, can I assume that the lack of a response means that there isn't any real solution to this other than unmerging the cells?
Hi there,
It was a little odd, but I didn't return anything the first time I tried a manual .Find. Not sure what happened, but see if this makes sense. If .Find doesn't find the (in this case) Value sought, the Range returned is Nothing. Thus the failure on the second call. Rather than handle that in the called function, try calling .Find twice, handling in the calling procedure.
Sub Test2()
Dim r As Range
Dim rngCol As Range
Set r = RangeFound(ThisWorkbook.Worksheets("Test").Cells, _
"Test", _
ThisWorkbook.Worksheets("Test").Range("A1"), _
xlValues, _
xlWhole)
If Not r Is Nothing Then
Set rngCol = r.EntireColumn
Set r = Range(r, RangeFound(rngCol))
End If
If Not r Is Nothing Then
MsgBox r.Address
End If
End Sub
Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range
If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If
Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function
Hope that helps,
Mark
I would use :
Function F_findInSheet(sh As Worksheet) As Range
Set F_findInSheet = sh.Columns(1).Find("Test").Resize(sh.Cells(Rows.Count, 5).End(xlUp).Row)
End Function
Sub test()
Set r = F_findInSheet(Worksheets("Test"))
r.Select
End Sub
But the 'not unmerge' requirement is the solution's impediment.
EirikDaude
12-04-2013, 01:09 PM
Thanks for both of your suggestions! I don't have access to my Office-computer right now, but I'll give your methods a try as soon as possible. I am sure they'll work perfectly :)
Eirik,
I am changing the tile of this thread to make it easier for those with a similar problem to find a solution.
or simpler:
Sub test()
Sheets("Test").Cells(F_findInSheet(Worksheets("Test"), 1, "Test"), 1).Select
End Sub
Function F_findInSheet(sh As Worksheet, y, c00)
F_findInSheet = Application.Match(c00, sh.UsedRange.Columns(y).Value, 0)
End Function
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.