VBA - 查找粗体行中的单元格 [英] VBA - Find cell in row which is bold

问题描述

假设我有以下数据格式：

我现在搜索单词"text"的代码。然后检索它下面的字段，直到它到达一个空单元格。

 Dim ra As Range 
 Dim s As String 
 
设置wb = ThisWorkbook 
设置ra = wb.Sheets（1）.Cells.Find（What：=" text"，LookIn：= xlFormulas，LookAt _ 
：= xlPart，SearchOrder：= xlByRows， SearchDirection：= xlNext，MatchCase：= _ 
 False，SearchFormat：= False）
如果ra是Nothing那么
 MsgBox（"未找到"）
否则
 Row = ra.Cells.Row 
 Column = ra.Cells.Column 
 Last = wb.Sheets（1）.Cells（Row，Column）.End（xlDown）.Row 
 For x =行+ 1到最后
 las = las& "，" &安培; wb.Worksheets（1）.Cells（x，Column）.Text 
 Next x 
 if las<> ""然后是
 wb.Sheets（"Sheet2"）。范围（"A1"）。值=中（las，3）
结束如果
结束如果

此代码返回：

这样可行。但问题出在这里。

如果我有这个数据格式：

代码返回：

这当然是代码的正常行为。但是，我想编码它应该在它找到一个空的单元格时停止或者如果一个单元格包含一个粗体的值。

有人知道怎么做吗？

提前致谢。

Ganesh

解决方案

我希望您的代码只是来自更大方法的不完整代码段，并且您已声明所有变量并使用Option Explicit。

要测试单元格是否格式化为粗体，您可以查询字体属性。例如

选项明确
 
公共子测试（）
 
 ActiveSheet.Range（" A12" ;）。Value = Concat（ActiveSheet.Range（" A2"）））
 ActiveSheet.Range（" B12"）。Value = Concat（ActiveSheet.Range（" B2"）））
 
结束子
 
公共函数Concat（AStartAt As Excel.Range）
 
 Dim Result As String 
 Dim CurrentCell As Excel.Range 
 Dim RowCount As Long 
 
 Result ="" 
 RowCount = 0 
 Do 
设置CurrentCell = AStartAt.Offset（RowCount）
如果Len（Trim（CurrentCell.Value））= 0或CurrentCell.Font.Bold那么
退出Do 
结束如果
 
结果=结果& "，" &安培; CurrentCell.Text 
 RowCount = RowCount + 1 
 Loop 
 
 Set CurrentCell = Nothing 
如果结果<> ""然后
结果=中（结果，3）
结束如果
 
 Concat =结果
 
结束函数
 

Hi,

Suppose I have the following dataformat:

The code that I have now searches for the word "text" and then retrieves the fields below it until it reaches an empty cell.

Dim ra As Range
    Dim s As String

    Set wb = ThisWorkbook
    Set ra = wb.Sheets(1).Cells.Find(What:="text", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If ra Is Nothing Then
        MsgBox ("Not Found")
    Else
        Row = ra.Cells.Row
        Column = ra.Cells.Column
        Last = wb.Sheets(1).Cells(Row, Column).End(xlDown).Row
        For x = Row + 1 To Last
            las = las & ", " & wb.Worksheets(1).Cells(x, Column).Text
        Next x
        If las  <> "" Then
            wb.Sheets("Sheet2").Range("A1").Value = Mid(las, 3)
        End If
    End If

This code returns:

So this works. But here is the problem.

If I have this dataformat:

The code returns:

This is, of course, the normal behaviour of the code. However, I want to code that it should stop when it finds a cell which is empty OR if a cell contains a value which is bold. 

Does someone know how to do this?

Thanks in advance.

Ganesh

解决方案

I hope your code is only an incomplete snippet from a larger method and you have declared all variables and you use Option Explicit.

To test whether the cell is formatted bold, you can query the Font property. E.g.

Option Explicit

Public Sub Test()

  ActiveSheet.Range("A12").Value = Concat(ActiveSheet.Range("A2"))
  ActiveSheet.Range("B12").Value = Concat(ActiveSheet.Range("B2"))

End Sub

Public Function Concat(AStartAt As Excel.Range)

  Dim Result As String
  Dim CurrentCell As Excel.Range
  Dim RowCount As Long
  
  Result = ""
  RowCount = 0
  Do
    Set CurrentCell = AStartAt.Offset(RowCount)
    If Len(Trim(CurrentCell.Value)) = 0 Or CurrentCell.Font.Bold Then
      Exit Do
    End If
    
    Result = Result & ", " & CurrentCell.Text
    RowCount = RowCount + 1
  Loop
        
  Set CurrentCell = Nothing
  If Result <> "" Then
    Result = Mid(Result, 3)
  End If
  
  Concat = Result
   
End Function

这篇关于VBA - 查找粗体行中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！