时间戳

首页 > 解决方案 > 需要一些帮助来修复 VBA 中的 For Each 循环

excel - 需要一些帮助来修复 VBA 中的 For Each 循环

问题描述

出于某种原因，此循环不会调用子 formatCells 在选择中的每个单元格上运行。它只会在所选范围内的左上角单元格上运行。 

Sub selectionLoop()

    Dim rng As Range, itm As Range
    Set rng = Selection

    For Each itm In rng
        Call formatCells
    Next

End Sub

Sub formatCells() 'Formats cells based on what is in the cell

    If WorksheetFunction.IsText(ActiveCell) = True Then 'Searching for text in the cell

        With ActiveCell.Font  'Applies text format
        .Name = "Calibri"
        .Size = 18
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
        .Bold = True
        End With

        With ActiveCell
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
    Else
        ActiveCell.NumberFormat = "#,##0_);(#,##0)"  'Applies number format
    End If



End Sub

标签： excelvba

解决方案

对您的代码的一些改进：

  1. 使用选项显式避免未声明变量的麻烦
  2. 将变量命名为有意义的名称
  3. 不要依赖 ActiveCell除非你是认真的
  4. 可选：替换你IF的Select Case
Option Explicit

Sub selectionLoop()

    Dim targetRange As Range
    Dim cell As Range

    Set targetRange = Selection

    ' Loop through each cell in range
    For Each cell In targetRange
        ' Pass the cell to procedure
        formatCells cell
    Next

End Sub

Private Sub formatCells(ByVal cell As Range) 'Formats cells based on what is in the cell

    If WorksheetFunction.IsText(cell.Value) = True Then 'Searching for text in the cell

        With cell.Font  'Applies text format
        .Name = "Calibri"
        .Size = 18
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
        .Bold = True
        End With

        With cell
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
    Else
        cell.NumberFormat = "#,##0_);(#,##0)"  'Applies number format
    End If

End Sub

推荐阅读