Count value when the value is a merged cell

[Macaron]

Hello, I'm a newbie to type VBA code.
Recently, I would like to create a series of VBA code in order to accelerate work.
Now, I have faced the problems below:
1. I would like to count the number of "LTG" which has 3 merged rows only.(shown in the uploaded image)
2. About the Find Function, I discovered that when "Remark" has merged 2 columns,
Find Function didn't work, so I have to find "*" or unmerged "Remark" columns. Is there any ways I can find the range of Remark columns within border area. I just want to the range which is below "Remark"(Range("X11")) to last row with border area(Range("X42")).

Sub Countnumber()
    Dim objNewWorkbook As Workbook
    Dim objNewWorksheet As Worksheet
    Dim LastColumn As Long
    Set objNewWorkbook = Excel.Application.Workbooks.Add
    Set objNewWorksheet = objNewWorkbook.Sheets(1)
    
    For i = 1 To ThisWorkbook.Sheets.Count
    objNewWorksheet.Cells(i, 1) = ThisWorkbook.Sheets(i).Name
    LastColumn = ThisWorkbook.Sheets(i).Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    objNewWorksheet.Cells(i, 6).Value = WorksheetFunction.CountIf(ThisWorkbook.Sheets(i).Columns(LastColumn), "*LTG*")
    
    Next i
End Sub

Hope someone can help me..

 

[Alex Blakenburg]

See if changing Wrap to True gives you what you need.

    'new counting item
    With Application.FindFormat
        .WrapText = True        ' <---- Changed from False to True
        .ShrinkToFit = False
        .MergeCells = True
    End With

 

[Macaron]

See if changing Wrap to True gives you what you need.

    'new counting item
    With Application.FindFormat
        .WrapText = True        ' <---- Changed from False to True
        .ShrinkToFit = False
        .MergeCells = True
    End With

I followed your instruction. It is successful in "DB1" sheet, failed in "DB" sheet, it still show the result 8

In the "DB" sheet,
I think that my expressed code cannot really count part of string "LIGHT" on that sheet

Else
            If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value = "*LIGHT*" Then
            cntTPNLTG = cntTPNLTG + 1
            End If

If I want to find part of string (LIGHT), is it right to express it as "*LIGHT*"?

 

[Alex Blakenburg]

I followed your instruction. It is successful in "DB1" sheet, failed in "DB" sheet, it still show the result 8

In the "DB" sheet,
I think that my expressed code cannot really count part of string "LIGHT" on that sheet

Else
            If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value = "*LIGHT*" Then
            cntTPNLTG = cntTPNLTG + 1
            End If

If I want to find part of string (LIGHT), is it right to express it as "*LIGHT*"?

Replace the = sign with the word LIKE

foundOtherTPNCell.Value LIKE "*LIGHT*"

 

[Macaron]

Replace the = sign with the word LIKE

foundOtherTPNCell.Value LIKE "*LIGHT*"

It still cannot work Or is it related to version of excel again?

 

[Alex Blakenburg]

Now that you have changed it to LIKE, the issue is not with the line itself. You are only showing a part of the code and if you look at the whole statement you will see that it in inside another IF statement which is so structured that it is never going to get to execute the line cntTPNLTG = cntTPNLTG + 1

The code is currently:

            If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value <> "SPARE" And foundOtherTPNCell.Value <> "SPACE" Then
                cntOtherTPNMerged = cntOtherTPNMerged + 1
            Else
                If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value = "*LIGHT*" Then
                    cntTPNLTG = cntTPNLTG + 1
                End If
            End If

Since LIGHT is NOT SPARE and NOT SPACE, it will execute the first part of the stament cntOtherTPNMerged = cntOtherTPNMerged + 1 and never make it to the ELSE part of the statement.
If you want LIGHT counted in both accumulators then make them 2 totally separate IF statements. If you don't want it in cntOtherTPNMerged then do the LIGHT IF statement first and put cntOtherTPNMerged in the ELSE part.

 

[Macaron]

Now that you have changed it to LIKE, the issue is not with the line itself. You are only showing a part of the code and if you look at the whole statement you will see that it in inside another IF statement which is so structured that it is never going to get to execute the line cntTPNLTG = cntTPNLTG + 1

The code is currently:

            If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value <> "SPARE" And foundOtherTPNCell.Value <> "SPACE" Then
                cntOtherTPNMerged = cntOtherTPNMerged + 1
            Else
                If foundOtherTPNCell.MergeArea.Rows.Count = 3 And foundOtherTPNCell.Value = "*LIGHT*" Then
                    cntTPNLTG = cntTPNLTG + 1
                End If
            End If

Since LIGHT is NOT SPARE and NOT SPACE, it will execute the first part of the stament cntOtherTPNMerged = cntOtherTPNMerged + 1 and never make it to the ELSE part of the statement.
If you want LIGHT counted in both accumulators then make them 2 totally separate IF statements. If you don't want it in cntOtherTPNMerged then do the LIGHT IF statement first and put cntOtherTPNMerged in the ELSE part.

Thanks for your explanation! I have used another way to solve it. Sorry, I have another question. How can I find text within a specific range. I want to set a range that help me to count data below“Remark” cell and in BorderAround. I tried to use find format and find function to count, but it seems didn’t work.

  With Application.FindFormat .Borders(xlEdgeRight).LineStyle= True .Borders(xlEdgeLeft).LineStyle= True .Borders(xlEdgeTop).LineStyle = True .Borders(xlEdgeBottom).LineStyle = True End With

 

[Alex Blakenburg]

Thanks for your explanation! I have used another way to solve it. Sorry, I have another question. How can I find text within a specific range. I want to set a range that help me to count data below“Remark” cell and in BorderAround. I tried to use find format and find function to count, but it seems didn’t work.

  With Application.FindFormat .Borders(xlEdgeRight).LineStyle= True .Borders(xlEdgeLeft).LineStyle= True .Borders(xlEdgeTop).LineStyle = True .Borders(xlEdgeBottom).LineStyle = True End With

Start with the code below from my post #19.
Change the With Application.FindFormat to suit your new requirements.
Set rng = to what area you want searched.
the last line Set foundCell should work as is.
The looping part of the code should be the same as used with and following on from the code below.

    With Application.FindFormat
        .WrapText = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
   
    Set rng = ActiveSheet.UsedRange
       
    Set foundCell = rng.Find(What:="", after:=rng.Cells(1, 1), LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows,

 

[Macaron]

Start with the code below from my post #19.
Change the With Application.FindFormat to suit your new requirements.
Set rng = to what area you want searched.
the last line Set foundCell should work as is.
The looping part of the code should be the same as used with and following on from the code below.

    With Application.FindFormat
        .WrapText = False
        .ShrinkToFit = False
        .MergeCells = True
    End With
  
    Set rng = ActiveSheet.UsedRange
      
    Set foundCell = rng.Find(What:="", after:=rng.Cells(1, 1), LookIn:=xlFormulas2, LookAt:= _
        xlPart, SearchOrder:=xlByRows,

Thanks, I used another stupid way to solve it :P. I have faced another problem again.
When I run my code on the sheet that "Remark" have merged columns, my code cannot count data in merged columns, how can I solve it?

 

[Macaron]

Thanks, I used another stupid way to solve it :P. I have faced another problem again.
When I run my code on the sheet that "Remark" have merged columns, my code cannot count data in merged columns, how can I solve it?

I found this information on other post “The content defaults to the top left cell of the merged selection, with all of the other cells being empty (this default can not be changed).”

Therefore, shall I write a if statement that count the left cell when it is a merged columns?

 

[Alex Blakenburg]

The same method will work for columns as for rows. The only difference is that you need to include "all" the columns covered by the merged cells.
(The safest would be to use the full range eg Set rng = sht.UsedRange)
If like your check for rows = 3 you wanted to do the same for columns it would become

If foundOtherTPNCell.MergeArea.Columns.Count = 3