[RESOLVED] [excel 365] Looping through cells but skipping the merged cells

[Corpo]

Hey, so I am working on some code that watches a range for cell value changes, then runs a macro based off a data validation list. At first I had it watch just single cell changes, and it worked that way just fine. But in testing the guys who use it found it would be nice if they could copy and paste the list from one workbook to another. This is for invoicing based off field work.

so here is my code presently

The problem I suppose is in the watched area it is merged with rows B and C. So it is looping through each cell as if they aren't just a single cell.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SPN, rng As Range
    Dim wb As String
    
    Set SPN = Target.Parent.Range("A16:A39")             'Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
    
        'If Target.Count > 1 Then Exit Sub                   'Only look at single cell changes
        If Intersect(Target, SPN) Is Nothing Then Exit Sub  'Only look at that range
    
        'Action if Condition(s) are met (do your thing here...)
        On Error GoTo error
        For Each rng In Target
        rng.Offset(0, 1).Value = "=iferror(vlookup(" & rng.MergeArea.Cells(1).Address & ",tblspn,2,false),"""")&"" """
        rng.Offset(0, 8).Value = "=iferror(vlookup(" & rng.mergaarea.Cells(1).Address & ",tblspn,3,false),"""")&"" """
        Select Case rng.Value
            Case Is = "SLB-CAN-DAY"
                rng.Offset(0, 6).Value = "=sum(q22,q28)"
            Case Is = "SLB-CAN-STDBY"
                rng.Offset(0, 6).Value = "=sum(q23,q29)"
            Case Is = "SLB-CAN-KM"
                rng.Offset(0, 6).Value = "=sum(q24,q30)"
            Case Is = "SLB-CAN-HOTEL"
                rng.Offset(0, 6).Value = "=sum(q25,q31)"
            Case Is = "SLB-CAN-SUBSIS"
                rng.Offset(0, 6).Value = "=sum(q26,q32)"
            Case Is = "" 'this case is here to clean the sheet if a spn code is removed from the line
                rng.Offset(0, 1).Value = ""
                rng.Offset(0, 6).Value = ""
                rng.Offset(0, 8).Value = ""
            Case Else   'this case removes the 'sum' formulas from spn codes that don't require it. but this would essentially loop over top of the spn codes that do require it since it is looping through the merged area.
                rng.Offset(0, 6).Value = ""
        End Select
        Next rng
        
Exit Sub
        
error:
    Target.Offset(0, 1).Value = ""
    Target.Offset(0, 6).Value = ""
    Target.Offset(0, 8).Value = ""
End Sub

I find what is happening that it will first put my correct formula's in, then it gets to the select case part and eventually sees that cells in row "B" have a empty value then sweep through and delete the formulas that are supposed to be in.

on searching my issue I see a lot of people that use vba say stay away from merged cells, lol. But the cells are merged so other cells can look nice, and so the users of the book don't get confused. Also, I'm still quite novice at vba.

[Corpo]

test.zip

here is two test files i did. one works by only watching single cell changes. the other works but it watches all 3 columns in the merged area and ends up removing the formulas

[Zvoni]

IIRC the value in a "merged"-cell is in the upper-left cell, so if you have a block of cells merged together, say A1 to D2 (2 rows across 4 columns), the value (and any formulas you might have) would be in A1

https://stackoverflow.com/questions/...address-in-vba

59

When I don't know the bounds of the merged area, I get the value with Range("B6").MergeArea.Cells(1,1).Value

[westconn1]

you could try a criteria to see if the cell is the first cell in the merge area

        For Each rng In Target
        rng.Offset(0, 1).Value = "=iferror(vlookup(" & rng.MergeArea.Cells(1).Address & ",tblspn,2,false),"""")&"" """
        rng.Offset(0, 8).Value = "=iferror(vlookup(" & rng.mergaarea.Cells(1).Address & ",tblspn,3,false),"""")&"" """
        Select Case rng.Value
          If rng.Address = rng.MergeArea(1).address     ' this should avoid processing any cell that is not the primary cell in the merge area
            Case Is = "SLB-CAN-DAY"
                rng.Offset(0, 6).Value = "=sum(q22,q28)"
            Case Is = "SLB-CAN-STDBY"
                rng.Offset(0, 6).Value = "=sum(q23,q29)"
            Case Is = "SLB-CAN-KM"
                rng.Offset(0, 6).Value = "=sum(q24,q30)"
            Case Is = "SLB-CAN-HOTEL"
                rng.Offset(0, 6).Value = "=sum(q25,q31)"
            Case Is = "SLB-CAN-SUBSIS"
                rng.Offset(0, 6).Value = "=sum(q26,q32)"
            Case Is = "" 'this case is here to clean the sheet if a spn code is removed from the line
                rng.Offset(0, 1).Value = ""
                rng.Offset(0, 6).Value = ""
                rng.Offset(0, 8).Value = ""
            Case Else   'this case removes the 'sum' formulas from spn codes that don't require it. but this would essentially loop over top of the spn codes that do require it since it is looping through the merged area.
                rng.Offset(0, 6).Value = ""
        End Select
      End If
   Next rng

this is untested, so test with care

[Zvoni]

Hmm, or treat each range as if it is part of a MergeArea, and refer to Cells(1,1)

[Corpo]

you could try a criteria to see if the cell is the first cell in the merge area

        For Each rng In Target
        rng.Offset(0, 1).Value = "=iferror(vlookup(" & rng.MergeArea.Cells(1).Address & ",tblspn,2,false),"""")&"" """
        rng.Offset(0, 8).Value = "=iferror(vlookup(" & rng.mergaarea.Cells(1).Address & ",tblspn,3,false),"""")&"" """
        Select Case rng.Value
          If rng.Address = rng.MergeArea(1).address     ' this should avoid processing any cell that is not the primary cell in the merge area
            Case Is = "SLB-CAN-DAY"
                rng.Offset(0, 6).Value = "=sum(q22,q28)"
            Case Is = "SLB-CAN-STDBY"
                rng.Offset(0, 6).Value = "=sum(q23,q29)"
            Case Is = "SLB-CAN-KM"
                rng.Offset(0, 6).Value = "=sum(q24,q30)"
            Case Is = "SLB-CAN-HOTEL"
                rng.Offset(0, 6).Value = "=sum(q25,q31)"
            Case Is = "SLB-CAN-SUBSIS"
                rng.Offset(0, 6).Value = "=sum(q26,q32)"
            Case Is = "" 'this case is here to clean the sheet if a spn code is removed from the line
                rng.Offset(0, 1).Value = ""
                rng.Offset(0, 6).Value = ""
                rng.Offset(0, 8).Value = ""
            Case Else   'this case removes the 'sum' formulas from spn codes that don't require it. but this would essentially loop over top of the spn codes that do require it since it is looping through the merged area.
                rng.Offset(0, 6).Value = ""
        End Select
      End If
   Next rng

this is untested, so test with care

you're my hero. that did it, thanks!