+ Reply to Thread
Results 1 to 3 of 3

Range.Find with merged cells

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    09-17-2014
    Location
    UK
    MS-Off Ver
    2007
    Posts
    117

    Range.Find with merged cells

    I keep getting completely random results using the .find method with a sheet containing merged cells:

    I've tried all combinations of
    xlpart, xlwhole
    xlvalues, xlformulas
    expanded the range to include the merged cells
    set range as all column A, column A & B

    but I can't get it to work consistently, if at all.

    I could get it to work if I searched the whole sheet without specifying a range, however there are likely to be more instances over time so I don't want it to find them as it will screw up the range.

    I know merged cells are an absolute pain, however they need to remain that way in this case.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Range.Find with merged cells

    Little amendment to your code

    With r
        Set acell = .Find(What:=thisyr, LookIn:=xlValues, LookAt:=xlWhole)
        
         If Not acell Is Nothing Then
           Debug.Print acell.Address
         End If
    End With
    You also need to format your dates to year or month. The code will not find 12 or 2015.

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Range.Find with merged cells

    Merged cells are paid in the a**. The only way I can get to work this code is:
    First unmerge cells, find the value (Address) and then once you are done, you can re-merge them.

    Option Explicit
    Sub cellfind()
    
    Dim dws As Worksheet
    Dim r As Range, acell As Range
    Dim dte As String
    Dim thismth As Date
    Dim thisyr As Date
    Dim dlastrow As Long
    
    thismth = Month(Date)
    thisyr = Year(Date)
    
    Set dws = Sheets(1)
    
    dlastrow = dws.Cells(Rows.Count, "B").End(xlUp).Row
    Set r = dws.Range(dws.Cells(1, 1), dws.Cells(dlastrow, 2))
    Debug.Print dws.Cells(1, 1).Value
    Debug.Print r.Address
    
    With Columns("A:B")
        Range(.Cells(1, 1), .Cells(1, 2)).UnMerge
        Set acell = .Find(What:="2015", LookIn:=xlValues, LookAt:=xlWhole)
        
         If Not acell Is Nothing Then
           Debug.Print acell.Address
         End If
         
         
          Range(.Cells(1, 1), .Cells(1, 2)).Merge
    End With
    
    
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA - To find the word 'Numeric' in merged cells
    By gan_xl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 12:10 PM
  2. [SOLVED] Find all merged cells via conditional formatting?
    By se15 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-14-2013, 03:57 PM
  3. Using Range.find() doesn't work when the text you look for is in merged cells
    By nfuids in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-17-2012, 03:24 PM
  4. if find merged cell in a range change the reference sheet value to 0
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2011, 07:41 AM
  5. Excel 2008 : Find merged cells: mac
    By hulasweets in forum Excel General
    Replies: 1
    Last Post: 08-13-2010, 04:08 PM
  6. Find the last cell with merged cells
    By TraceyD in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2010, 03:10 PM
  7. Find doesn't work w/ merged cells
    By dlh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-17-2008, 03:03 PM
  8. how can I find merged cells in a large xl-file
    By tvanellen in forum Excel General
    Replies: 5
    Last Post: 05-07-2006, 08:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1