For每个循环给出错误(Next没有For)

Heading_Value = "L"
For Each cell In Range(Heading_Start, Heading_End)

cell.Value = Heading_Value

If Heading_Value = "L" Then
Heading_Value = "R"
If Heading_Value = "R" Then
Heading_Value = "Total"
Else
Heading_Value = "L"
End If
Next cell

范围内的两个变量对应于单元格地址，例如"Heading Start"= Range " a5;

不知道为什么这是说下一个没有我猜我做错了嵌套的If语句

基本上它应该通过一个范围(假设第1行a:F

应该看起来像

<表类>BCDEFtbody><<tr>TR总TR总

您缺少End If关闭外部If Heading_Value = "L" Then

Else
Heading_Value = "L"
End If
End If  <-- here
Next cell

FromFill a RowtoFill a Range

始终使用Option Explicit。它将迫使您声明几乎所有的变量，并反过来在编译时检测大多数错误。
引用工作表对于能够限定所有范围至关重要，例如
```
Set hrg = ws.Range(hStartCell, hEndCell) ' focus on 'ws.'
```
这样他们就不会从错误的工作表中得到引用。
如果代码不能在多个工作表上运行，请显式地指定工作表，例如
```
Set ws = ThisWorkbook.Sheets("Sheet1") ' (tab) name
```
或者用
更好
```
Set ws = Sheet1 ' code name
```

否则，请确保存在活动工作表，并且它不是图表:

If ActiveSheet Is Nothing Then Exit Sub ' no visible workbooks open
If Not TypeOf ActiveSheet Is Worksheet Then Exit Sub ' not a worksheet

主要

这是代码的开头，上面的一些已经实现了。
将' One of the ideas here!替换为以下任何"idea"的代码，或将idea中的' Main code here!替换为此代码。

Option Explicit
Sub Main()
' Reference the worksheet.
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Just an example.
Dim hStartCell As Range: Set hStartCell = ws.Range("A1")
Dim hEndCell As Range: Set hEndCell = ws.Range("K1")
' Reference the range: qualify with worksheet ('ws.')
Dim hrg As Range: Set hrg = ws.Range(hStartCell, hEndCell)
' One of the ideas here!
End Sub

基本

您需要记住在循环之前设置hIndex = 1。
您需要记住在最后一个案例中添加hIndex = 0。
增加病例并不简单。

Sub Idea1()
' Main code here!
Dim hIndex As Long: hIndex = 1
Dim cell As Range
For Each cell In hrg.Cells
Select Case hIndex
Case 1: cell.Value = "L"
Case 2: cell.Value = "R"
Case 3: cell.Value = "Total": hIndex = 0
End Select
hIndex = hIndex + 1
Next cell
End Sub

引入Mod操作符解决了之前的一些问题，但是添加更多的情况仍然不够简单。

Sub Idea2()
' Main code here!
Dim cell As Range, hIndex As Long
For Each cell In hrg.Cells
hIndex = hIndex Mod 3
Select Case hIndex
Case 0: cell.Value = "L"
Case 1: cell.Value = "R"
Case 2: cell.Value = "Total"
End Select
hIndex = hIndex + 1
Next cell
End Sub

通过简单地向数组中添加项目来添加更多的案例。
注意这是如何工作的任何单行或单列范围。

Sub Idea3()
' 'VBA.' ensures a zero-based array.
Dim Headers(): Headers = VBA.Array("L", "R", "Total")
' Main code here!
Dim hCount As Long: hCount = UBound(Headers) + 1
Dim cell As Range, hIndex As Long
For Each cell In hrg.Cells
cell.Value = Headers(hIndex Mod hCount)
hIndex = hIndex + 1
Next cell
End Sub

通过将代码放在单独的过程中，相关代码被减少到一行，使您的主过程更具可读性。

Sub Idea4()
Dim Headers(): Headers = Array("L", "R", "Total")
' Main code here!
WriteRepeatingHeaders hrg, Headers
End Sub
Sub WriteRepeatingHeadersRange( _
ByVal HeaderRange As Range, _
Headers() As Variant)
' *** These 'complications' ensure that a 1D array of any lower limit (base)
' is accepted.
Dim hLB As Long: hLB = LBound(Headers) '***
Dim hCount As Long: hCount = UBound(Headers) - hLB + 1 '***
Dim cell As Range, hIndex As Long
For Each cell In HeaderRange.Cells
cell.Value = Headers(hIndex Mod hCount + hLB) '***
hIndex = hIndex + 1
Next cell
End Sub

大(最终)

通过引入一个数组来将结果写入它，并最终将它们一次性写入范围，代码变得更高效(更快)。
通过引入第三个参数(默认为False)，您现在可以为相同的多行和多列范围获得不同的结果。

Sub Final()
Dim Headers(): Headers = Array("L", "R", "Total")

Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
' Just an example.
Dim hStartCell As Range: Set hStartCell = ws.Range("A1")
Dim hEndCell As Range: Set hEndCell = ws.Range("K1")
Dim hrg As Range: Set hrg = ws.Range(hStartCell, hEndCell)
WriteRepeatingValues hrg, Headers
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      Repeatedly writes the values from a 1D array to a range.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub WriteRepeatingValues( _
ByVal DestinationRange As Range, _
Values() As Variant, _
Optional ByVal ByColumns As Boolean = False)

Dim vLB As Long: vLB = LBound(Values)
Dim vCount As Long: vCount = UBound(Values) - vLB + 1

With DestinationRange

Dim rCount As Long: rCount = .Rows.Count
Dim cCount As Long: cCount = .Columns.Count
Dim Data(): ReDim Data(1 To rCount, 1 To cCount)

Dim r As Long, c As Long, vIndex As Long

If ByColumns Then
For c = 1 To cCount
For r = 1 To rCount
Data(r, c) = Values(vIndex Mod vCount + vLB)
vIndex = vIndex + 1
Next r
Next c
Else ' by rows
For r = 1 To rCount
For c = 1 To cCount
Data(r, c) = Values(vIndex Mod vCount + vLB)
vIndex = vIndex + 1
Next c
Next r
End If

.Value = Data

End With
End Sub

找到解决方法

Heading_Value = 1
For Each cell In Range(Heading_Start, Heading_End)

cell.Value = "H" & Heading_Value

If Heading_Value = 3 Then
Heading_Value = 0
End If

Heading_Value = Heading_Value + 1

Next cell
With Range(Heading_Start, Heading_End)
.Replace what:="H1", Replacement:="L"
.Replace what:="H2", Replacement:="R"
.Replace what:="H3", Replacement:="Total"
End With

FromFill a RowtoFill a Range

相关内容

最新更新

热门标签：