Results 1 to 4 of 4

Error trying to loop through merged cells in a range?

Threaded View

kermit_d_frog Error trying to loop through... 01-21-2022, 12:16 PM
AB33 Re: Error trying to loop... 01-21-2022, 12:28 PM
jolivanes Re: Error trying to loop... 01-21-2022, 01:48 PM
jolivanes Re: Error trying to loop... 01-21-2022, 11:59 PM
  1. #1
    Registered User
    Join Date
    05-31-2019
    Location
    Shelbyville, IN
    MS-Off Ver
    2016
    Posts
    24

    Error trying to loop through merged cells in a range?

    I have an excel form that has lots of merged cells. I cannot unmerge the cells due to the nature of the form. What I want to do is loop through the merged cells in column A (see image below). If that cell contains data but the adjacent cells in column B or Column C do not then I want to exit sub. Also, if everything is OK and all the cells have data then I want to continue on with different code. I'm having an issue that because the cells are merged, even if the cells all contain data like they are supposed to it is treating it as though they do not have data. Below is the Code and an image from my workbook. I also tried to attach a sample WB but not sure if it worked. I tried adding ".mergearea" to my X variable but that did not work either. I appreciate any help on this.

    Sub test1()
    
    Dim sht1 As Worksheet
        Set sht1 = ThisWorkbook.Sheets("Sheet1")
    Dim x As Range
    
    With sht1
            For Each x In Range("test_rng") 'test_rng is named range on worksheet
                If x <> "" And x.Offset(0, 16) = "" Or x <> "" And x.Offset(0, 25) = "" Then
                    MsgBox "Data missing, try again!"
                    x.Interior.ColorIndex = 19
                    Exit Sub
                Else
                    x.MergeArea.Interior.ColorIndex = 0
                    MsgBox "Success!"
                End If
            Next x
    End With
    
    End Sub
    Capture.JPG
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 01-24-2020, 12:10 PM
  2. Merged cell error on copy/paste, but no merged cells
    By OmniBlue in forum Excel General
    Replies: 3
    Last Post: 07-15-2019, 02:15 PM
  3. [SOLVED] Clear merged and non-merged cells in named range.
    By IMM Tech in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2018, 12:37 PM
  4. Replies: 1
    Last Post: 10-22-2016, 04:27 AM
  5. Problems with loop and cells merged
    By sylvain133 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2013, 06:03 AM
  6. [SOLVED] nested loop for copying range: error no cells were found
    By Edejager in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2013, 08:52 AM
  7. Error: Cannot change the part of merged cell - When trying to lock cells in range
    By Ranjani in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-09-2009, 12:03 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