PDA

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?

GTO
12-03-2013, 11:46 PM
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

snb
12-04-2013, 04:09 AM
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 :)

SamT
12-06-2013, 11:40 AM
Eirik,

I am changing the tile of this thread to make it easier for those with a similar problem to find a solution.

snb
12-06-2013, 01:48 PM
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