回圈事件-根據單元格中的前導空格值應用單元格或行格式

A 列包含大量文本值，其中有前導空格。根據空間值，我想將格式應用于范圍的單元格或行。對于選擇，我找到了一種查找縮進級別的方法； cell.offset(0,-1).value=cell.IndentLevel。

如何使用這種方法找到領先的空間計數？將.value的成為.formul一個？有一些方法可以在包含=Find(Left(Trim(A1),1),A1)-1的單元格中找到空間計數作為公式，但是在 vba 中制定這個公式對我來說有點棘手。

或許有辦法申請。value=cell.xx找到前導空格值，以便我可以根據需要應用格式？有什么建議？發送！

uj5u.com熱心網友回復：

如果您想使用上面的公式，您可以通過多種方式執行此操作，但要演示與單元格版本的解耦，您可以這樣做...

Public Sub GetSpacesUsingFindFunctionInVBA()
    Dim strText As String, intSpaceCount As Integer
    
    strText = "    Four spaces"
    
    intSpaceCount = WorksheetFunction.Find(Left(Trim(strText), 1), strText) - 1
    
    Debug.Print intSpaceCount
End Sub

這也將起作用...

intSpaceCount = InStr(1, strText, Trim(strText)) - 1

那里應該有足夠的空間讓您適應回圈中的要求。

uj5u.com熱心網友回復：

計算前導空格

功能

Option Explicit

Function LeadingSpacesCount( _
    ByVal SearchString As String, _
    Optional ByVal ExcludeSpacesOnly As Boolean = False) _
As Long
    Dim slen As Long: slen = Len(SearchString)
    If slen = 0 Then Exit Function
    Dim ltLen As Long: ltLen = Len(LTrim(SearchString))
    Dim lsLen As Long: lsLen = slen - ltLen
    If ExcludeSpacesOnly Then
        If lsLen = slen Then Exit Function
    End If
    LeadingSpacesCount = lsLen
End Function

使用 Excel

= LeadingSpacesCount(A2)

或者，要排除僅包含空格的單元格：

= LeadingSpacesCount(A2,TRUE)

用法 VBA

• 調整常量部分中的值并改進作業表參考。

Sub WriteSpacesCount()
    
    Const sCol As String = "A" ' Source
    Const dCol As String = "G" ' Destination
    Const fRow As Long = 2 ' e.g. first row is headers
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
    If lRow < fRow Then Exit Sub ' no data
    
    Dim rg As Range: Set rg = ws.Cells(fRow, sCol).Resize(lRow - fRow   1)
    
    Dim Data As Variant
    If rg.Rows.Count = 1 Then
        ReDim Data(1 To 1, 1 To 1): Data(1, 1) = rg.Value
    Else
        Data = rg.Value
    End If
    
    Dim r As Long
    
    For r = 1 To UBound(Data, 1)
        Data(r, 1) = LeadingSpacesCount(CStr(Data(r, 1)))
    Next r
    
    rg.EntireRow.Columns(dCol).Value = Data
    
End Sub

uj5u.com熱心網友回復：

我的代碼比建議的要長一點，但是下面是成功執行并評估 A 列所有行中的前導空格的代碼，然后應用文本和單元格格式：

Sub WFP_Proj_Resource()
    Dim i As Long
    Dim sh As Worksheet
    Dim sheetArr As Variant
    Dim CellText As Variant

    Set sh = ThisWorkbook.Sheets("Sheet1")
    sheetArr = sh.UsedRange
    rowC = sh.UsedRange.Rows.Count
    
'Loop through rows in column A, measure the leading space before the text (not indent) and apply format
     For i = 1 To rowC
      CellText = sheetArr(i, 1)
      intSpaceCount = WorksheetFunction.Find(Left(Trim(CellText), 1), CellText) - 1
            If intSpaceCount = "0" Then
                sh.Rows(i).Interior.Color = vbBlue
                sh.Rows(i).Font.Color = vbWhite
                sh.Rows(i).Font.Bold = True
                
             ElseIf intSpaceCount = "1" Then
                sh.Cells(i, 1).Interior.ColorIndex = 37
            
             ElseIf intSpaceCount = "2" Then
                sh.Rows(i).Interior.ColorIndex = 11
                sh.Rows(i).Font.Color = vbWhite
                sh.Rows(i).Font.Bold = True
                
            ElseIf intSpaceCount = "3" Then
                sh.Cells(i, 1).Interior.ColorIndex = 12
         End If
    Next i   
End Sub

標籤：

上一篇：EXCELVBA豎排文本

下一篇：將Excel資料上傳到Mysql資料庫時保持正確的時間格式